#!/usr/bin/env python3 """ i4Fuel deal pipeline v2 — single-file CLI. Companies and contacts are first-class entities (independent of deals). Inquiries are the pre-deal layer — every inbound FCO/RFQ lands here first. Incidents track scams, no-shows, breaches against companies over time. Auto-checks on company creation: - Local blacklist folder scan (Dropbox/Petrol trade/Blacklists/) - OFAC SDN list match (cached weekly) Storage: SQLite on Dropbox. Dashboard: auto-regenerated HTML. """ import argparse import csv import json import os import shutil import sqlite3 import sys import textwrap import urllib.request from datetime import datetime, timedelta, timezone from pathlib import Path # ── Paths (all self-contained under the script directory on VPS) ────────────── SCRIPT_DIR = Path(__file__).resolve().parent DB_PATH = SCRIPT_DIR / "i4fuel.db" DASHBOARD_PATH = SCRIPT_DIR / "dashboard.html" ACTIVE_DIR = SCRIPT_DIR / "ACTIVE" CLOSED_DIR = SCRIPT_DIR / "CLOSED" LOCAL_BLACKLIST_DIR = SCRIPT_DIR / "blacklists" CACHE_DIR = SCRIPT_DIR / "cache" OFAC_CACHE = CACHE_DIR / "ofac_sdn.csv" OFAC_URL = "https://www.treasury.gov/ofac/downloads/sdn.csv" OFAC_REFRESH_DAYS = 7 DEAL_SUBFOLDERS = [ "01_Inquiry", "02_Mandate", "03_Offer", "04_Contract", "05_Verification", "06_Payment", "07_Commission", "08_Closed", ] STAGES = { 1: "Inquiry", 2: "Mandate / LOI", 3: "Offer (ICPO/FCO)", 4: "Contract (SPA)", 5: "Verification / POP", 6: "Payment", 7: "Commission settlement", 8: "Closed", } ENTITIES = { "i4fuel": {"name": "i4Fuel BV", "kvk": "97064874"}, "hertsberg": {"name": "Hertsberg Ventures BV", "kvk": "82687978"}, "glaya": {"name": "Glaya Holding BV", "kvk": "82618464"}, } COMMODITY_CODES = { "EN590": "EN590 10ppm Diesel", "JA1": "Jet Fuel A1", "D6": "D6 Virgin Fuel Oil", "CRUDE": "Crude Oil", "LNG": "LNG", "SUGAR": "Brazil Sugar ICUMSA 45", "SBO": "Crude Soybean Oil", "SFOL": "Sunflower Oil", "BITUM": "Bitumen", "CHEM": "Chemicals", "LUBE": "Lubricants", } PARTY_ROLES = ["buyer", "seller", "intermediary", "mandate", "broker", "paymaster"] TRUST_LEVELS = ["unknown", "verified", "suspect", "scammer", "blacklisted"] DD_STATUSES = ["not_checked", "in_progress", "passed", "failed", "flagged"] INQUIRY_STATUSES = ["open", "in_review", "converted", "rejected", "no_response", "stale"] INQUIRY_TYPES = ["RFQ", "FCO", "SCO", "LOI", "introduction", "cold_outreach"] INCIDENT_TYPES = [ "scam_attempt", "fake_offer", "no_show", "payment_default", "contract_breach", "suspicious_doc", "blacklist_hit", "ofac_hit", "rejected_inquiry", "other", ] # ── DB ──────────────────────────────────────────────────────────────────────── SCHEMA = """ CREATE TABLE IF NOT EXISTS companies ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, name_normalized TEXT NOT NULL, country TEXT, registration_no TEXT, address TEXT, website TEXT, email TEXT, phone TEXT, trust_level TEXT NOT NULL DEFAULT 'unknown', dd_status TEXT NOT NULL DEFAULT 'not_checked', dd_score TEXT, dd_report_path TEXT, dd_checked_at TEXT, ofac_status TEXT NOT NULL DEFAULT 'not_checked', ofac_checked_at TEXT, ofac_hit_details TEXT, blacklist_folder_match TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_companies_normalized ON companies(name_normalized); CREATE INDEX IF NOT EXISTS idx_companies_trust ON companies(trust_level); CREATE INDEX IF NOT EXISTS idx_companies_dd ON companies(dd_status); CREATE TABLE IF NOT EXISTS contacts ( id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER REFERENCES companies(id) ON DELETE CASCADE, name TEXT NOT NULL, email TEXT, phone TEXT, title TEXT, passport_no TEXT, nationality TEXT, notes TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_contacts_company ON contacts(company_id); CREATE TABLE IF NOT EXISTS inquiries ( id INTEGER PRIMARY KEY AUTOINCREMENT, direction TEXT NOT NULL, type TEXT NOT NULL, channel TEXT NOT NULL, company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL, contact_id INTEGER REFERENCES contacts(id) ON DELETE SET NULL, raw_company_name TEXT, raw_contact TEXT, commodity_code TEXT, volume_mt REAL, monthly_mt REAL, price_usd REAL, price_unit TEXT, incoterm TEXT, origin TEXT, port TEXT, payment_terms TEXT, received_at TEXT, status TEXT NOT NULL DEFAULT 'open', rejection_reason TEXT, deal_id TEXT REFERENCES deals(id) ON DELETE SET NULL, next_action TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_inquiries_status ON inquiries(status); CREATE INDEX IF NOT EXISTS idx_inquiries_company ON inquiries(company_id); CREATE TABLE IF NOT EXISTS incidents ( id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE, date TEXT NOT NULL, type TEXT NOT NULL, description TEXT, related_inquiry_id INTEGER REFERENCES inquiries(id) ON DELETE SET NULL, related_deal_id TEXT REFERENCES deals(id) ON DELETE SET NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_incidents_company ON incidents(company_id); CREATE TABLE IF NOT EXISTS deals ( id TEXT PRIMARY KEY, commodity_code TEXT NOT NULL, commodity TEXT NOT NULL, counterparty_company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL, counterparty_name TEXT NOT NULL, our_role TEXT NOT NULL, our_entity TEXT NOT NULL, stage INTEGER NOT NULL DEFAULT 1, volume_mt REAL, monthly_mt REAL, price_usd REAL, price_unit TEXT, incoterm TEXT, port TEXT, origin TEXT, payment_terms TEXT, folder_path TEXT, inquiry_id INTEGER REFERENCES inquiries(id) ON DELETE SET NULL, next_action TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, closed_at TEXT, deal_type TEXT NOT NULL DEFAULT 'single', buy_leg_stage INTEGER, sell_leg_stage INTEGER, sell_leg_company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL, sell_leg_counterparty TEXT ); CREATE INDEX IF NOT EXISTS idx_deals_stage ON deals(stage); CREATE INDEX IF NOT EXISTS idx_deals_company ON deals(counterparty_company_id); CREATE TABLE IF NOT EXISTS deal_parties ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT NOT NULL REFERENCES deals(id) ON DELETE CASCADE, company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE, contact_id INTEGER REFERENCES contacts(id) ON DELETE SET NULL, role TEXT NOT NULL, bank_name TEXT, bank_iban TEXT, bank_swift TEXT, bank_account_name TEXT, notes TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_dealparties_deal ON deal_parties(deal_id); CREATE INDEX IF NOT EXISTS idx_dealparties_company ON deal_parties(company_id); CREATE TABLE IF NOT EXISTS commissions ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT NOT NULL REFERENCES deals(id) ON DELETE CASCADE, deal_party_id INTEGER NOT NULL REFERENCES deal_parties(id) ON DELETE CASCADE, amount_per_unit REAL NOT NULL, unit TEXT NOT NULL, total_amount REAL, status TEXT NOT NULL DEFAULT 'agreed', due_at TEXT, paid_at TEXT, notes TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_commissions_deal ON commissions(deal_id); CREATE TABLE IF NOT EXISTS documents ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT REFERENCES deals(id) ON DELETE CASCADE, inquiry_id INTEGER REFERENCES inquiries(id) ON DELETE CASCADE, doc_type TEXT NOT NULL, stage INTEGER, filename TEXT, filepath TEXT, signed INTEGER NOT NULL DEFAULT 0, sent_at TEXT, received_at TEXT, notes TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_documents_deal ON documents(deal_id); CREATE TABLE IF NOT EXISTS comms_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT REFERENCES deals(id) ON DELETE CASCADE, inquiry_id INTEGER REFERENCES inquiries(id) ON DELETE CASCADE, company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL, contact_id INTEGER REFERENCES contacts(id) ON DELETE SET NULL, direction TEXT NOT NULL, channel TEXT NOT NULL, subject TEXT, summary TEXT, timestamp TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_commslog_deal ON comms_log(deal_id); CREATE INDEX IF NOT EXISTS idx_commslog_inquiry ON comms_log(inquiry_id); CREATE TABLE IF NOT EXISTS stage_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT NOT NULL REFERENCES deals(id) ON DELETE CASCADE, from_stage INTEGER, to_stage INTEGER NOT NULL, changed_at TEXT NOT NULL, notes TEXT, leg TEXT ); CREATE TABLE IF NOT EXISTS deal_legs ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT NOT NULL REFERENCES deals(id) ON DELETE CASCADE, leg_type TEXT NOT NULL, leg_index INTEGER NOT NULL DEFAULT 1, company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL, counterparty TEXT NOT NULL, stage INTEGER NOT NULL DEFAULT 1, volume_mt REAL, price_usd REAL, price_unit TEXT DEFAULT 'MT', notes TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_deal_legs_deal ON deal_legs(deal_id); """ def db(): conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row conn.execute("PRAGMA foreign_keys = ON") return conn def init_db(): SCRIPT_DIR.mkdir(parents=True, exist_ok=True) ACTIVE_DIR.mkdir(exist_ok=True) CLOSED_DIR.mkdir(exist_ok=True) CACHE_DIR.mkdir(exist_ok=True) with db() as conn: conn.executescript(SCHEMA) migrate_db() def migrate_db(): """Add columns / tables introduced after initial schema — safe to run on every startup.""" col_migrations = [ "ALTER TABLE deals ADD COLUMN deal_type TEXT NOT NULL DEFAULT 'single'", "ALTER TABLE deals ADD COLUMN buy_leg_stage INTEGER", "ALTER TABLE deals ADD COLUMN sell_leg_stage INTEGER", "ALTER TABLE deals ADD COLUMN sell_leg_company_id INTEGER REFERENCES companies(id)", "ALTER TABLE deals ADD COLUMN sell_leg_counterparty TEXT", "ALTER TABLE stage_history ADD COLUMN leg TEXT", ] with db() as conn: for sql in col_migrations: try: conn.execute(sql) except Exception: pass # column already exists # Create deal_legs table if missing (SCHEMA handles new installs; this covers upgrades) conn.executescript(""" CREATE TABLE IF NOT EXISTS deal_legs ( id INTEGER PRIMARY KEY AUTOINCREMENT, deal_id TEXT NOT NULL REFERENCES deals(id) ON DELETE CASCADE, leg_type TEXT NOT NULL, leg_index INTEGER NOT NULL DEFAULT 1, company_id INTEGER REFERENCES companies(id) ON DELETE SET NULL, counterparty TEXT NOT NULL, stage INTEGER NOT NULL DEFAULT 1, volume_mt REAL, price_usd REAL, price_unit TEXT DEFAULT 'MT', notes TEXT, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_deal_legs_deal ON deal_legs(deal_id); """) # Migrate existing B2B deals from flat columns → deal_legs (idempotent) b2b_deals = conn.execute( "SELECT * FROM deals WHERE deal_type = 'back_to_back'" ).fetchall() for d in b2b_deals: existing = conn.execute( "SELECT COUNT(*) FROM deal_legs WHERE deal_id = ?", (d['id'],) ).fetchone()[0] if existing == 0: ts = now() conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, created_at) VALUES (?,?,?,?,?,?,?,?,?,?)""", (d['id'], 'buy', 1, d['counterparty_company_id'], d['counterparty_name'], d['buy_leg_stage'] or 1, d['volume_mt'], d['price_usd'], d['price_unit'] or 'MT', ts), ) conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, created_at) VALUES (?,?,?,?,?,?,?,?,?,?)""", (d['id'], 'sell', 1, d['sell_leg_company_id'], d['sell_leg_counterparty'] or '?', d['sell_leg_stage'] or 1, d['volume_mt'], d['price_usd'], d['price_unit'] or 'MT', ts), ) def now(): return datetime.now(timezone.utc).isoformat(timespec="seconds") def get_legs(conn, deal_id): """Return deal_legs rows for a deal, ordered buy first then sell by index.""" return conn.execute( "SELECT * FROM deal_legs WHERE deal_id = ? ORDER BY leg_type DESC, leg_index ASC", (deal_id,), ).fetchall() def update_summary_stage(conn, deal_id, ts): """Recompute deals.stage = min stage across all legs (lagging leg determines kanban position).""" legs = get_legs(conn, deal_id) if not legs: return min_stage = min(l['stage'] for l in legs) conn.execute("UPDATE deals SET stage = ?, updated_at = ? WHERE id = ?", (min_stage, ts, deal_id)) def next_sell_index(conn, deal_id): row = conn.execute( "SELECT MAX(leg_index) FROM deal_legs WHERE deal_id = ? AND leg_type = 'sell'", (deal_id,), ).fetchone() return (row[0] or 0) + 1 # ── External checks ────────────────────────────────────────────────────────── def normalize(s): if not s: return "" return "".join(c.lower() for c in s if c.isalnum()) def ensure_ofac_cache(): """Download OFAC SDN list if missing or older than OFAC_REFRESH_DAYS.""" needs_refresh = True if OFAC_CACHE.exists(): age = datetime.now() - datetime.fromtimestamp(OFAC_CACHE.stat().st_mtime) if age.days < OFAC_REFRESH_DAYS: needs_refresh = False if needs_refresh: try: print(f" fetching OFAC SDN list...", end=" ", flush=True) req = urllib.request.Request(OFAC_URL, headers={"User-Agent": "i4fuel-pipeline/2"}) with urllib.request.urlopen(req, timeout=15) as r: data = r.read() OFAC_CACHE.write_bytes(data) print(f"ok ({len(data)//1024} kB)") except Exception as e: print(f"failed ({e}); using stale cache if available") return OFAC_CACHE.exists() def ofac_check(company_name): """Substring match against OFAC SDN list. Returns (status, details).""" if not ensure_ofac_cache(): return ("not_checked", "OFAC list unavailable") target = normalize(company_name) if len(target) < 4: return ("not_checked", "Name too short for safe match") hits = [] with open(OFAC_CACHE, encoding="utf-8", errors="replace") as f: reader = csv.reader(f) for row in reader: if len(row) < 2: continue sdn_name = row[1].strip().strip('"') if normalize(sdn_name) and target in normalize(sdn_name): hits.append(sdn_name) if len(hits) >= 5: break elif normalize(sdn_name) and len(sdn_name) > 4 and normalize(sdn_name) in target: hits.append(sdn_name) if len(hits) >= 5: break if hits: return ("hit", " | ".join(hits)) return ("clear", None) def blacklist_folder_check(company_name): """Substring match against local blacklist folder names. Returns matched folder or None.""" if not LOCAL_BLACKLIST_DIR.exists(): return None target = normalize(company_name) if len(target) < 4: return None for entry in LOCAL_BLACKLIST_DIR.iterdir(): if not entry.is_dir(): continue folder = entry.name n = normalize(folder) if not n or len(n) < 4: continue if target in n or n in target: return folder return None # ── Helpers ────────────────────────────────────────────────────────────────── def prompt(label, default=None, required=False, options=None): while True: suffix = f" [{default}]" if default is not None else "" if options: suffix += f" ({'/'.join(options)})" val = input(f"{label}{suffix}: ").strip() if not val and default is not None: return default if not val and not required: return None if not val and required: print(" required.") continue if options and val not in options: print(f" must be one of: {', '.join(options)}") continue return val def prompt_float(label, default=None, required=False): while True: val = prompt(label, default=default, required=required) if val is None: return None try: return float(val) except ValueError: print(" must be a number.") def prompt_int(label, default=None, required=False): while True: val = prompt(label, default=default, required=required) if val is None: return None try: return int(val) except ValueError: print(" must be an integer.") def slug(text): return "".join(c if c.isalnum() else "-" for c in text).strip("-")[:30] def next_deal_seq(commodity_code): year = datetime.now().year with db() as conn: row = conn.execute( "SELECT COUNT(*) AS n FROM deals WHERE id LIKE ?", (f"{year}-%-{commodity_code}-%",), ).fetchone() return row["n"] + 1 def make_deal_id(commodity_code, counterparty): year = datetime.now().year seq = next_deal_seq(commodity_code) return f"{year}-{seq:02d}-{commodity_code}-{slug(counterparty).upper()[:10]}" def create_deal_folders(deal_id, commodity, counterparty): name = f"{deal_id}_{slug(commodity)}_{slug(counterparty)}" folder = ACTIVE_DIR / name folder.mkdir(parents=True, exist_ok=True) for sub in DEAL_SUBFOLDERS: (folder / sub).mkdir(exist_ok=True) return folder def find_company_by_name(name): n = normalize(name) with db() as conn: row = conn.execute("SELECT * FROM companies WHERE name_normalized = ?", (n,)).fetchone() return row def create_company_with_checks(name, country=None, registration_no=None, address=None, website=None, email=None, phone=None, notes=None): """Create company and run blacklist + OFAC checks. Returns (company_id, warnings).""" warnings = [] ts = now() bl_match = blacklist_folder_check(name) ofac_status, ofac_details = ofac_check(name) ofac_ts = ts if ofac_status != "not_checked" else None trust_level = "unknown" if bl_match or ofac_status == "hit": trust_level = "blacklisted" if bl_match: warnings.append(f"BLACKLIST FOLDER MATCH: {bl_match}") if ofac_status == "hit": warnings.append(f"OFAC HIT: {ofac_details}") with db() as conn: cur = conn.execute( """INSERT INTO companies (name, name_normalized, country, registration_no, address, website, email, phone, trust_level, ofac_status, ofac_checked_at, ofac_hit_details, blacklist_folder_match, notes, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (name, normalize(name), country, registration_no, address, website, email, phone, trust_level, ofac_status, ofac_ts, ofac_details, bl_match, notes, ts, ts), ) cid = cur.lastrowid # Auto-log incidents for any hits if bl_match: conn.execute( "INSERT INTO incidents (company_id, date, type, description, created_at) VALUES (?,?,?,?,?)", (cid, ts, "blacklist_hit", f"Folder match: {bl_match}", ts), ) if ofac_status == "hit": conn.execute( "INSERT INTO incidents (company_id, date, type, description, created_at) VALUES (?,?,?,?,?)", (cid, ts, "ofac_hit", ofac_details, ts), ) return cid, warnings # ── Inquiry commands ───────────────────────────────────────────────────────── def cmd_inquiry_new(args): print("=" * 60) print("New inquiry") print("=" * 60) direction = prompt("Direction", required=True, options=["inbound", "outbound"]) inq_type = prompt("Type", required=True, options=INQUIRY_TYPES) channel = prompt("Channel", required=True, options=["email", "teams", "whatsapp", "phone", "broker"]) company_name = prompt("Counterparty company name", required=True) existing = find_company_by_name(company_name) company_id = None warnings = [] if existing: company_id = existing["id"] print(f" → linked to existing company #{existing['id']} (trust: {existing['trust_level']}, DD: {existing['dd_status']})") else: print(" company not in DB — creating with auto-checks...") country = prompt(" Country") reg = prompt(" Registration / KVK no") addr = prompt(" Address") web = prompt(" Website") email = prompt(" Company email") phone = prompt(" Phone") company_id, warnings = create_company_with_checks( company_name, country, reg, addr, web, email, phone ) for w in warnings: print(f" ⚠ {w}") if warnings: print(" → company created as 'blacklisted'. Override with: pipeline.py company trust ") contact_raw = prompt("Contact person — name") contact_email = prompt("Contact email") contact_phone = prompt("Contact phone") contact_title = prompt("Contact title / role") # If we have a contact name and a company, promote into the contacts table contact_id = None if contact_raw and company_id: with db() as conn: existing = conn.execute( "SELECT id FROM contacts WHERE company_id = ? AND name = ?", (company_id, contact_raw), ).fetchone() if existing: contact_id = existing['id'] else: cur = conn.execute( """INSERT INTO contacts (company_id, name, email, phone, title, created_at) VALUES (?,?,?,?,?,?)""", (company_id, contact_raw, contact_email, contact_phone, contact_title, now()), ) contact_id = cur.lastrowid print(f" → contact #{contact_id} created at {company_name}") print("\nDeal terms (as proposed in the inquiry):") commodity_code = prompt(" Commodity code", options=list(COMMODITY_CODES.keys())) volume_mt = prompt_float(" Volume MT") monthly_mt = prompt_float(" Monthly MT") price_usd = prompt_float(" Price USD") price_unit = prompt(" Price unit", default="MT") incoterm = prompt(" Incoterm") origin = prompt(" Origin") port = prompt(" Port") payment_terms = prompt(" Payment terms", default="MT103/TT") next_action = prompt("Next action") notes = prompt("Notes") ts = now() with db() as conn: cur = conn.execute( """INSERT INTO inquiries (direction, type, channel, company_id, contact_id, raw_company_name, raw_contact, commodity_code, volume_mt, monthly_mt, price_usd, price_unit, incoterm, origin, port, payment_terms, received_at, status, next_action, notes, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (direction, inq_type, channel, company_id, contact_id, company_name, contact_raw, commodity_code, volume_mt, monthly_mt, price_usd, price_unit, incoterm, origin, port, payment_terms, ts, "open", next_action, notes, ts, ts), ) iid = cur.lastrowid print(f"\n✓ Inquiry #{iid} logged.") if warnings: print(" ⚠ Counterparty has blacklist/OFAC flags — review before progressing.") regenerate_dashboard() def cmd_inquiry_list(args): where = "WHERE status = 'open'" if not args.all else "" with db() as conn: rows = conn.execute( f"""SELECT i.*, c.trust_level, c.dd_status FROM inquiries i LEFT JOIN companies c ON c.id = i.company_id {where} ORDER BY created_at DESC""" ).fetchall() if not rows: print("No inquiries.") return print(f"\n{'ID':>4s} {'Dir':8s} {'Type':6s} {'Status':12s} " f"{'Commodity':12s} {'Counterparty':28s} {'Trust':12s} {'DD':12s}") print("-" * 110) for r in rows: print(f"{r['id']:>4d} {r['direction']:8s} {r['type']:6s} {r['status']:12s} " f"{(r['commodity_code'] or '—'):12s} {(r['raw_company_name'] or '')[:28]:28s} " f"{(r['trust_level'] or '—'):12s} {(r['dd_status'] or '—'):12s}") def cmd_inquiry_show(args): with db() as conn: r = conn.execute( """SELECT i.*, c.trust_level, c.dd_status, c.name AS company_name FROM inquiries i LEFT JOIN companies c ON c.id = i.company_id WHERE i.id = ?""", (args.id,) ).fetchone() if not r: print(f"Inquiry {args.id} not found.") sys.exit(1) print(f"\n{'=' * 70}\n Inquiry #{r['id']}\n{'=' * 70}") print(f" Direction: {r['direction']}") print(f" Type: {r['type']}") print(f" Channel: {r['channel']}") print(f" Status: {r['status']}") print(f" Company: {r['company_name'] or r['raw_company_name']} (#{r['company_id'] or '?'})") print(f" Trust / DD: {r['trust_level'] or '—'} / {r['dd_status'] or '—'}") print(f" Contact: {r['raw_contact'] or '—'}") print(f" Commodity: {r['commodity_code'] or '—'}") print(f" Volume: {r['volume_mt'] or '—'} MT (monthly {r['monthly_mt'] or '—'} MT)") print(f" Price: {r['price_usd'] or '—'} USD/{r['price_unit'] or '—'}") print(f" Incoterm: {r['incoterm'] or '—'}") print(f" Origin/Port: {r['origin'] or '—'} → {r['port'] or '—'}") print(f" Payment: {r['payment_terms'] or '—'}") print(f" Received: {r['received_at'][:16] if r['received_at'] else '—'}") print(f" Next action: {r['next_action'] or '—'}") print(f" Deal ID: {r['deal_id'] or '—'}") if r['rejection_reason']: print(f" Rejection: {r['rejection_reason']}") if r['notes']: print(f" Notes: {r['notes']}") def cmd_inquiry_convert(args): with db() as conn: inq = conn.execute("SELECT * FROM inquiries WHERE id = ?", (args.id,)).fetchone() if not inq: print(f"Inquiry {args.id} not found.") sys.exit(1) if inq['status'] == 'converted': print(f"Inquiry already converted to deal {inq['deal_id']}.") sys.exit(1) if not inq['company_id']: print("Inquiry has no linked company. Link one first: pipeline.py inquiry link") sys.exit(1) company = conn.execute("SELECT * FROM companies WHERE id = ?", (inq['company_id'],)).fetchone() print(f"Converting inquiry #{inq['id']} to deal.") print(f"Counterparty: {company['name']} (trust: {company['trust_level']}, DD: {company['dd_status']})") if company['trust_level'] in ('scammer', 'blacklisted'): if not args.force: print(f"⚠ Counterparty is {company['trust_level']}. Use --force to override.") sys.exit(1) print(f"⚠ Proceeding despite {company['trust_level']} flag.") if company['dd_status'] != 'passed': if not args.force: print(f"⚠ DD status is '{company['dd_status']}'. Run DD first or use --force.") sys.exit(1) print(f"⚠ Proceeding without passed DD.") deal_type = prompt("Deal type", default="single", options=["single", "back_to_back"]) our_entity = prompt("Our entity", default="hertsberg", options=list(ENTITIES.keys())) commodity_code = inq['commodity_code'] or prompt("Commodity code", required=True, options=list(COMMODITY_CODES.keys())) commodity = COMMODITY_CODES[commodity_code] if deal_type == "back_to_back": our_role = "back_to_back" else: our_role = prompt("Our role", required=True, options=["buyer", "seller", "intermediary"]) buy_price = prompt_float("Buy price USD/MT") or inq['price_usd'] notes = prompt("Notes (optional, in addition to inquiry notes)") deal_id = make_deal_id(commodity_code, company['name']) folder = create_deal_folders(deal_id, commodity, company['name']) ts = now() with db() as conn: conn.execute( """INSERT INTO deals (id, commodity_code, commodity, counterparty_company_id, counterparty_name, our_role, our_entity, stage, volume_mt, monthly_mt, price_usd, price_unit, incoterm, port, origin, payment_terms, folder_path, inquiry_id, notes, created_at, updated_at, deal_type) VALUES (?,?,?,?,?,?,?,1,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (deal_id, commodity_code, commodity, company['id'], company['name'], our_role, our_entity, inq['volume_mt'], inq['monthly_mt'], buy_price, inq['price_unit'], inq['incoterm'], inq['port'], inq['origin'], inq['payment_terms'], str(folder), inq['id'], notes, ts, ts, deal_type), ) conn.execute( """INSERT INTO deal_parties (deal_id, company_id, role, created_at) VALUES (?,?,?,?)""", (deal_id, company['id'], "seller" if deal_type == "back_to_back" or our_role == "buyer" else "buyer", ts), ) conn.execute( "INSERT INTO stage_history (deal_id, from_stage, to_stage, changed_at, notes) VALUES (?,?,?,?,?)", (deal_id, None, 1, ts, f"Converted from inquiry #{inq['id']}"), ) conn.execute( "UPDATE inquiries SET status = 'converted', deal_id = ?, updated_at = ? WHERE id = ?", (deal_id, ts, inq['id']), ) if deal_type == "back_to_back": conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, created_at) VALUES (?,?,?,?,?,?,?,?,?,?)""", (deal_id, 'buy', 1, company['id'], company['name'], 1, inq['volume_mt'], buy_price, inq['price_unit'] or 'MT', ts), ) print(f"\n Buy leg set: {company['name']} @ ${buy_price}/MT") print(" Now add sell leg(s). Enter 0 to finish.") sell_idx = 1 while True: sell_cid = prompt_int(f" Sell leg {sell_idx} buyer company ID (0 to finish)", required=True) if not sell_cid: break with db() as conn2: sc = conn2.execute("SELECT * FROM companies WHERE id = ?", (sell_cid,)).fetchone() if not sc: print(f" Company {sell_cid} not found. Skip.") continue sell_vol = prompt_float(f" Volume MT (default {inq['volume_mt']})") sell_price = prompt_float(" Sell price USD/MT") sell_notes = prompt(" Notes") conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, notes, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)""", (deal_id, 'sell', sell_idx, sc['id'], sc['name'], 1, sell_vol or inq['volume_mt'], sell_price, inq['price_unit'] or 'MT', sell_notes, ts), ) conn.execute( "INSERT INTO deal_parties (deal_id, company_id, role, created_at) VALUES (?,?,?,?)", (deal_id, sc['id'], "buyer", ts), ) print(f" ✓ Sell leg {sell_idx}: {sc['name']} @ ${sell_price}/MT") sell_idx += 1 if prompt(" Add another sell leg?", default="n", options=["y", "n"]) != "y": break update_summary_stage(conn, deal_id, ts) print(f"\n✓ Converted to deal {deal_id}") print(f" Folder: {folder}") regenerate_dashboard() def cmd_inquiry_reject(args): reason = args.reason or prompt("Rejection reason", required=True) log_incident = prompt("Log as incident on company? (scam/fake/etc.)", default="n", options=["y", "n"]) == "y" incident_type = None if log_incident: print("Incident types:", ", ".join(INCIDENT_TYPES)) incident_type = prompt("Incident type", required=True, options=INCIDENT_TYPES) ts = now() with db() as conn: inq = conn.execute("SELECT * FROM inquiries WHERE id = ?", (args.id,)).fetchone() if not inq: print(f"Inquiry {args.id} not found.") sys.exit(1) conn.execute( "UPDATE inquiries SET status = 'rejected', rejection_reason = ?, updated_at = ? WHERE id = ?", (reason, ts, args.id), ) if log_incident and inq['company_id']: conn.execute( """INSERT INTO incidents (company_id, date, type, description, related_inquiry_id, created_at) VALUES (?,?,?,?,?,?)""", (inq['company_id'], ts, incident_type, reason, args.id, ts), ) # If incident is severe, bump trust_level if incident_type in ("scam_attempt", "fake_offer", "payment_default"): conn.execute( "UPDATE companies SET trust_level = 'suspect', updated_at = ? WHERE id = ? AND trust_level = 'unknown'", (ts, inq['company_id']), ) print(f"✓ Inquiry #{args.id} rejected.") regenerate_dashboard() def cmd_inquiry_link(args): """Link inquiry to existing company.""" with db() as conn: inq = conn.execute("SELECT * FROM inquiries WHERE id = ?", (args.id,)).fetchone() company = conn.execute("SELECT * FROM companies WHERE id = ?", (args.company_id,)).fetchone() if not inq or not company: print("Inquiry or company not found.") sys.exit(1) conn.execute( "UPDATE inquiries SET company_id = ?, updated_at = ? WHERE id = ?", (args.company_id, now(), args.id), ) print(f"✓ Inquiry #{args.id} linked to company #{args.company_id} ({company['name']}).") regenerate_dashboard() # ── Company commands ──────────────────────────────────────────────────────── def cmd_company_new(args): print("=" * 60) print("New company") print("=" * 60) name = prompt("Company name", required=True) existing = find_company_by_name(name) if existing: print(f"⚠ Company already exists: #{existing['id']} {existing['name']}") sys.exit(1) country = prompt("Country") reg = prompt("Registration / KVK no") addr = prompt("Address") web = prompt("Website") email = prompt("Email") phone = prompt("Phone") notes = prompt("Notes") print("\nRunning blacklist + OFAC checks...") cid, warnings = create_company_with_checks(name, country, reg, addr, web, email, phone, notes) for w in warnings: print(f" ⚠ {w}") print(f"\n✓ Company #{cid} created.") if warnings: print(f" → trust_level set to 'blacklisted'. Review and override if needed.") regenerate_dashboard() def cmd_company_list(args): if args.needs_dd: where = "WHERE dd_status IN ('not_checked', 'in_progress', 'flagged')" elif args.flagged: where = "WHERE trust_level IN ('suspect', 'scammer', 'blacklisted')" else: where = "" with db() as conn: rows = conn.execute( f"SELECT * FROM companies {where} ORDER BY updated_at DESC" ).fetchall() if not rows: print("No companies.") return print(f"\n{'ID':>4s} {'Name':30s} {'Country':12s} {'Trust':12s} {'DD':12s} {'OFAC':10s} {'BL':5s}") print("-" * 95) for r in rows: bl = "✓" if r['blacklist_folder_match'] else "" print(f"{r['id']:>4d} {r['name'][:30]:30s} {(r['country'] or '—')[:12]:12s} " f"{r['trust_level']:12s} {r['dd_status']:12s} {r['ofac_status']:10s} {bl:5s}") def cmd_company_show(args): with db() as conn: c = conn.execute("SELECT * FROM companies WHERE id = ?", (args.id,)).fetchone() if not c: print(f"Company {args.id} not found.") sys.exit(1) contacts = conn.execute( "SELECT * FROM contacts WHERE company_id = ?", (args.id,) ).fetchall() incidents = conn.execute( "SELECT * FROM incidents WHERE company_id = ? ORDER BY date DESC", (args.id,) ).fetchall() deals = conn.execute( "SELECT id, stage, commodity FROM deals WHERE counterparty_company_id = ? OR id IN (SELECT deal_id FROM deal_parties WHERE company_id = ?)", (args.id, args.id), ).fetchall() inquiries = conn.execute( "SELECT id, status, type, commodity_code FROM inquiries WHERE company_id = ?", (args.id,) ).fetchall() print(f"\n{'=' * 70}\n Company #{c['id']}: {c['name']}\n{'=' * 70}") print(f" Country: {c['country'] or '—'}") print(f" Registration: {c['registration_no'] or '—'}") print(f" Address: {c['address'] or '—'}") print(f" Website / Mail: {c['website'] or '—'} / {c['email'] or '—'}") print(f" Phone: {c['phone'] or '—'}") print(f" Trust level: {c['trust_level']}") print(f" DD status: {c['dd_status']} (score: {c['dd_score'] or '—'})") print(f" DD report: {c['dd_report_path'] or '—'}") print(f" DD checked at: {c['dd_checked_at'] or '—'}") print(f" OFAC status: {c['ofac_status']} {f'({c['ofac_hit_details']})' if c['ofac_hit_details'] else ''}") print(f" Blacklist: {c['blacklist_folder_match'] or 'no match'}") if c['notes']: print(f" Notes: {c['notes']}") print(f"\n Contacts ({len(contacts)}):") for ct in contacts: print(f" [{ct['id']:3d}] {ct['name']:25s} {ct['title'] or '':20s} {ct['email'] or ''}") print(f"\n Incidents ({len(incidents)}):") for i in incidents: print(f" {i['date'][:10]} {i['type']:18s} {(i['description'] or '')[:60]}") print(f"\n Deals ({len(deals)}):") for d in deals: print(f" {d['id']:30s} stage {d['stage']} {d['commodity']}") print(f"\n Inquiries ({len(inquiries)}):") for i in inquiries: print(f" #{i['id']:>3d} {i['status']:12s} {i['type']:6s} {i['commodity_code'] or '—'}") def cmd_company_check(args): """Update DD status after running /supplier-check.""" with db() as conn: c = conn.execute("SELECT * FROM companies WHERE id = ?", (args.id,)).fetchone() if not c: print(f"Company {args.id} not found.") sys.exit(1) print(f"DD update for: {c['name']}") status = prompt("DD status", required=True, options=DD_STATUSES) score = None if status in ("passed", "failed", "flagged"): score = prompt("DD score", required=True, options=["low", "medium", "high"]) report = prompt("DD report file path (in Dropbox)") trust = prompt("Update trust level?", default="no", options=TRUST_LEVELS + ["no"]) ts = now() with db() as conn: conn.execute( """UPDATE companies SET dd_status = ?, dd_score = ?, dd_report_path = ?, dd_checked_at = ?, updated_at = ? WHERE id = ?""", (status, score, report, ts, ts, args.id), ) if trust != "no": conn.execute("UPDATE companies SET trust_level = ? WHERE id = ?", (trust, args.id)) print(f"✓ DD updated: {status}, score={score or '—'}") regenerate_dashboard() def cmd_company_trust(args): """Set trust level directly.""" trust = args.level or prompt("Trust level", required=True, options=TRUST_LEVELS) with db() as conn: conn.execute( "UPDATE companies SET trust_level = ?, updated_at = ? WHERE id = ?", (trust, now(), args.id), ) print(f"✓ Company #{args.id} trust level → {trust}") regenerate_dashboard() def cmd_company_ofac(args): """Re-run OFAC check.""" with db() as conn: c = conn.execute("SELECT * FROM companies WHERE id = ?", (args.id,)).fetchone() if not c: print(f"Company {args.id} not found.") sys.exit(1) print(f"Running OFAC check for: {c['name']}") status, details = ofac_check(c['name']) ts = now() with db() as conn: conn.execute( """UPDATE companies SET ofac_status = ?, ofac_checked_at = ?, ofac_hit_details = ?, updated_at = ? WHERE id = ?""", (status, ts, details, ts, args.id), ) if status == "hit": conn.execute( "INSERT INTO incidents (company_id, date, type, description, created_at) VALUES (?,?,?,?,?)", (args.id, ts, "ofac_hit", details, ts), ) conn.execute("UPDATE companies SET trust_level = 'blacklisted' WHERE id = ?", (args.id,)) print(f"✓ OFAC: {status} {'— ' + details if details else ''}") regenerate_dashboard() def cmd_company_incident(args): print(f"New incident for company #{args.id}") print("Types:", ", ".join(INCIDENT_TYPES)) itype = prompt("Type", required=True, options=INCIDENT_TYPES) desc = prompt("Description", required=True) ts = now() with db() as conn: conn.execute( """INSERT INTO incidents (company_id, date, type, description, created_at) VALUES (?,?,?,?,?)""", (args.id, ts, itype, desc, ts), ) print(f"✓ Incident logged.") regenerate_dashboard() # ── Contact commands ──────────────────────────────────────────────────────── def cmd_contact_new(args): with db() as conn: c = conn.execute("SELECT * FROM companies WHERE id = ?", (args.company_id,)).fetchone() if not c: print(f"Company {args.company_id} not found.") sys.exit(1) print(f"New contact at {c['name']}") name = prompt("Name", required=True) email = prompt("Email") phone = prompt("Phone") title = prompt("Title") passport = prompt("Passport no") nationality = prompt("Nationality") notes = prompt("Notes") with db() as conn: cur = conn.execute( """INSERT INTO contacts (company_id, name, email, phone, title, passport_no, nationality, notes, created_at) VALUES (?,?,?,?,?,?,?,?,?)""", (args.company_id, name, email, phone, title, passport, nationality, notes, now()), ) cid = cur.lastrowid print(f"✓ Contact #{cid} added.") regenerate_dashboard() # ── Deal commands ─────────────────────────────────────────────────────────── def cmd_deal_new(args): """Create deal directly (without going through inquiry — outbound proactive deals).""" print("=" * 60) print("New deal (direct — bypass inquiry layer)") print("=" * 60) print("Note: typically inbound deals start as 'inquiry new'. Use this for outbound origination only.\n") company_id = prompt_int("Counterparty company ID (from 'company list')", required=True) with db() as conn: c = conn.execute("SELECT * FROM companies WHERE id = ?", (company_id,)).fetchone() if not c: print(f"Company {company_id} not found.") sys.exit(1) print(f" → {c['name']} (trust: {c['trust_level']}, DD: {c['dd_status']})") if c['trust_level'] in ('scammer', 'blacklisted') or c['dd_status'] != 'passed': if prompt("Proceed anyway?", default="n", options=["y", "n"]) != "y": sys.exit(0) print("\nCommodity codes:", ", ".join(COMMODITY_CODES.keys())) commodity_code = prompt("Commodity code", required=True).upper() if commodity_code not in COMMODITY_CODES: print(f"Unknown code: {commodity_code}") sys.exit(1) commodity = COMMODITY_CODES[commodity_code] deal_type = prompt("Deal type", default="single", options=["single", "back_to_back"]) our_entity = prompt("Our entity", default="hertsberg", options=list(ENTITIES.keys())) if deal_type == "back_to_back": our_role = "back_to_back" else: our_role = prompt("Our role", required=True, options=["buyer", "seller", "intermediary"]) volume_mt = prompt_float("Trial volume MT") monthly_mt = prompt_float("Monthly volume MT") buy_price = prompt_float("Buy price USD/MT") price_unit = prompt("Price unit", default="MT") incoterm = prompt("Incoterm") port = prompt("Port") origin = prompt("Origin") payment_terms = prompt("Payment terms", default="MT103/TT") notes = prompt("Notes") deal_id = make_deal_id(commodity_code, c['name']) folder = create_deal_folders(deal_id, commodity, c['name']) ts = now() with db() as conn: conn.execute( """INSERT INTO deals (id, commodity_code, commodity, counterparty_company_id, counterparty_name, our_role, our_entity, stage, volume_mt, monthly_mt, price_usd, price_unit, incoterm, port, origin, payment_terms, folder_path, notes, created_at, updated_at, deal_type) VALUES (?,?,?,?,?,?,?,1,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (deal_id, commodity_code, commodity, company_id, c['name'], our_role, our_entity, volume_mt, monthly_mt, buy_price, price_unit, incoterm, port, origin, payment_terms, str(folder), notes, ts, ts, deal_type), ) conn.execute( "INSERT INTO deal_parties (deal_id, company_id, role, created_at) VALUES (?,?,?,?)", (deal_id, company_id, "seller" if deal_type == "back_to_back" or our_role == "buyer" else "buyer", ts), ) conn.execute( "INSERT INTO stage_history (deal_id, from_stage, to_stage, changed_at, notes) VALUES (?,?,?,?,?)", (deal_id, None, 1, ts, "Deal created (outbound)"), ) if deal_type == "back_to_back": conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, created_at) VALUES (?,?,?,?,?,?,?,?,?,?)""", (deal_id, 'buy', 1, company_id, c['name'], 1, volume_mt, buy_price, price_unit, ts), ) print(f"\n Buy leg set: {c['name']} @ ${buy_price}/MT") print(" Now add sell leg(s). Enter 'done' when finished.") sell_idx = 1 while True: print(f"\n Sell leg {sell_idx}:") sell_cid = prompt_int(" Buyer company ID (or 0 to finish)", required=True) if not sell_cid: break with db() as conn2: sc = conn2.execute("SELECT * FROM companies WHERE id = ?", (sell_cid,)).fetchone() if not sc: print(f" Company {sell_cid} not found. Skip.") continue sell_vol = prompt_float(f" Volume MT (default {volume_mt})") sell_price = prompt_float(" Sell price USD/MT") sell_notes = prompt(" Notes") conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, notes, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)""", (deal_id, 'sell', sell_idx, sc['id'], sc['name'], 1, sell_vol or volume_mt, sell_price, price_unit, sell_notes, ts), ) conn.execute( "INSERT INTO deal_parties (deal_id, company_id, role, created_at) VALUES (?,?,?,?)", (deal_id, sc['id'], "buyer", ts), ) print(f" ✓ Sell leg {sell_idx}: {sc['name']} @ ${sell_price}/MT") sell_idx += 1 if prompt(" Add another sell leg?", default="n", options=["y", "n"]) != "y": break update_summary_stage(conn, deal_id, ts) print(f"\n✓ Created deal {deal_id}") print(f" Folder: {folder}") regenerate_dashboard() def cmd_deal_list(args): closed = args.closed with db() as conn: rows = conn.execute( f"SELECT * FROM deals WHERE {'stage = 8' if closed else 'stage < 8'} ORDER BY updated_at DESC" ).fetchall() legs_by_deal = {} for leg in conn.execute("SELECT * FROM deal_legs ORDER BY leg_type DESC, leg_index ASC").fetchall(): legs_by_deal.setdefault(leg['deal_id'], []).append(leg) if not rows: print("No deals.") return print(f"\n{'ID':30s} {'Type':6s} {'Stage':20s} {'Commodity':18s} {'Counterparty':30s} {'Vol/MT':>10s}") print("-" * 120) for r in rows: legs = legs_by_deal.get(r['id'], []) if r['deal_type'] == 'back_to_back' and legs: buy = next((l for l in legs if l['leg_type'] == 'buy'), None) sells = [l for l in legs if l['leg_type'] == 'sell'] stage_label = f"buy:{buy['stage'] if buy else '?'} sell:{','.join(str(l['stage']) for l in sells)}" cp = f"{r['counterparty_name'][:10]}→{'+'.join(l['counterparty'][:8] for l in sells)}" vol = sum(l['volume_mt'] or 0 for l in sells) or r['volume_mt'] or 0 else: stage_label = f"{r['stage']}. {STAGES[r['stage']]}" cp = r['counterparty_name'][:30] vol = r['volume_mt'] or 0 print(f"{r['id']:30s} {'B2B' if r['deal_type']=='back_to_back' else ' ':6s} " f"{stage_label:20s} {r['commodity'][:18]:18s} {cp[:30]:30s} {vol:>10,.0f}") def cmd_deal_show(args): with db() as conn: deal = conn.execute("SELECT * FROM deals WHERE id = ?", (args.deal_id,)).fetchone() if not deal: print(f"Deal {args.deal_id} not found.") sys.exit(1) parties = conn.execute( """SELECT dp.*, c.name AS company_name, c.trust_level, c.dd_status FROM deal_parties dp JOIN companies c ON c.id = dp.company_id WHERE dp.deal_id = ? ORDER BY dp.id""", (args.deal_id,) ).fetchall() commissions = conn.execute( """SELECT cm.*, c.name AS party_name FROM commissions cm JOIN deal_parties dp ON dp.id = cm.deal_party_id JOIN companies c ON c.id = dp.company_id WHERE cm.deal_id = ? ORDER BY cm.id""", (args.deal_id,) ).fetchall() docs = conn.execute( "SELECT * FROM documents WHERE deal_id = ? ORDER BY created_at DESC", (args.deal_id,) ).fetchall() comms = conn.execute( "SELECT * FROM comms_log WHERE deal_id = ? ORDER BY timestamp DESC LIMIT 10", (args.deal_id,), ).fetchall() with db() as conn2: legs = get_legs(conn2, deal['id']) print(f"\n{'=' * 70}\n {deal['id']}\n{'=' * 70}") if deal['deal_type'] == 'back_to_back': print(f" Type: BACK-TO-BACK (summary stage: {deal['stage']}. {STAGES[deal['stage']]})") print(f" {'Leg':<6} {'#':<3} {'Counterparty':<30} {'Stage':<22} {'Vol/MT':>10} {'$/MT':>8}") print(f" {'-'*85}") for leg in legs: ltype = f"{leg['leg_type'].upper()}" print(f" {ltype:<6} {leg['id']:<3} {leg['counterparty']:<30} " f"{leg['stage']}. {STAGES.get(leg['stage'],'?'):<18} " f"{(leg['volume_mt'] or 0):>10,.0f} {(leg['price_usd'] or 0):>8,.2f}") print(f" Advance a leg: deal advance {deal['id']} --leg ") else: print(f" Stage: {deal['stage']}. {STAGES[deal['stage']]}") print(f" Counterparty: {deal['counterparty_name']} (#{deal['counterparty_company_id'] or '—'})") print(f" Our role: {deal['our_role']} ({ENTITIES[deal['our_entity']]['name']})") print(f" Entity: {ENTITIES[deal['our_entity']]['name']}") print(f" Commodity: {deal['commodity']}") print(f" Volume: {deal['volume_mt'] or '—'} MT trial / {deal['monthly_mt'] or '—'} MT monthly") print(f" Price: {deal['price_usd'] or '—'} USD/{deal['price_unit'] or '—'}") print(f" Incoterm: {deal['incoterm'] or '—'}") print(f" Port: {deal['port'] or '—'}") print(f" Origin: {deal['origin'] or '—'}") print(f" Payment: {deal['payment_terms'] or '—'}") print(f" Folder: {deal['folder_path']}") if deal['inquiry_id']: print(f" From inquiry: #{deal['inquiry_id']}") if deal['next_action']: print(f" Next action: {deal['next_action']}") if deal['notes']: print(f" Notes: {deal['notes']}") print(f"\n Parties ({len(parties)}):") for p in parties: print(f" [{p['id']:3d}] {p['role']:13s} {p['company_name']:30s} " f"trust={p['trust_level']:10s} DD={p['dd_status']}") if commissions: print(f"\n Commissions ({len(commissions)}):") for c in commissions: print(f" [{c['id']:3d}] {c['party_name']:30s} {c['amount_per_unit']:>8.2f}/{c['unit']:<3s} " f"total ${c['total_amount'] or 0:>10,.2f} {c['status'].upper()}") if docs: print(f"\n Documents ({len(docs)}):") for d in docs: signed = "✓" if d['signed'] else " " print(f" [{d['id']:3d}] [{signed}] {d['doc_type']:18s} {d['filename'] or '—'}") if comms: print(f"\n Recent comms ({len(comms)}):") for c in comms: arrow = "→" if c['direction'] == 'out' else "←" print(f" {c['timestamp'][:16]} {arrow} {c['channel']:8s} {(c['subject'] or '')[:50]}") print() def cmd_deal_advance(args): with db() as conn: deal = conn.execute("SELECT * FROM deals WHERE id = ?", (args.deal_id,)).fetchone() if not deal: print(f"Deal {args.deal_id} not found.") sys.exit(1) ts = now() if deal['deal_type'] == 'back_to_back': # --leg targets a specific deal_legs.id leg_id = getattr(args, 'leg', None) if not leg_id: legs = get_legs(conn, args.deal_id) print("Legs:") for l in legs: print(f" [{l['id']}] {l['leg_type'].upper()} {l['leg_index']} — " f"{l['counterparty']} stage {l['stage']}. {STAGES.get(l['stage'],'?')}") leg_id = prompt_int("Leg ID to advance", required=True) leg = conn.execute("SELECT * FROM deal_legs WHERE id = ? AND deal_id = ?", (leg_id, args.deal_id)).fetchone() if not leg: print(f"Leg {leg_id} not found on deal {args.deal_id}.") sys.exit(1) new_leg_stage = args.stage if args.stage else leg['stage'] + 1 if new_leg_stage > 8 or new_leg_stage < 1: print("Stage must be 1-8.") sys.exit(1) conn.execute("UPDATE deal_legs SET stage = ? WHERE id = ?", (new_leg_stage, leg_id)) conn.execute( "INSERT INTO stage_history (deal_id, from_stage, to_stage, changed_at, notes, leg) VALUES (?,?,?,?,?,?)", (args.deal_id, leg['stage'], new_leg_stage, ts, args.note, f"{leg['leg_type']}{leg['leg_index']}"), ) print(f"✓ {args.deal_id} [{leg['leg_type'].upper()} {leg['leg_index']} / {leg['counterparty']}]: " f"stage {leg['stage']} → {new_leg_stage} ({STAGES[new_leg_stage]})") # Commission trigger: any sell leg crossing stage 6 if leg['leg_type'] == 'sell': if new_leg_stage >= 6 and leg['stage'] < 6: n = conn.execute( "UPDATE commissions SET status = 'due', due_at = ? WHERE deal_id = ? AND status = 'agreed'", (ts, args.deal_id), ).rowcount if n: print(f" → {n} commission(s) auto-marked due.") elif new_leg_stage < 6 and leg['stage'] >= 6: n = conn.execute( "UPDATE commissions SET status = 'agreed', due_at = NULL WHERE deal_id = ? AND status = 'due'", (args.deal_id,), ).rowcount if n: print(f" → {n} commission(s) rolled back to agreed.") update_summary_stage(conn, args.deal_id, ts) # Close when ALL legs reach stage 8 all_legs = get_legs(conn, args.deal_id) if all(l['stage'] == 8 for l in all_legs): conn.execute("UPDATE deals SET stage = 8, closed_at = ? WHERE id = ?", (ts, args.deal_id)) folder = Path(deal['folder_path']) if folder.exists() and folder.parent == ACTIVE_DIR: dst = CLOSED_DIR / folder.name if not dst.exists(): shutil.move(str(folder), str(dst)) conn.execute("UPDATE deals SET folder_path = ? WHERE id = ?", (str(dst), args.deal_id)) print(f" → All legs closed. Deal moved to CLOSED.") else: new_stage = args.stage if args.stage else deal['stage'] + 1 if new_stage > 8 or new_stage < 1: print("Stage must be 1-8.") sys.exit(1) conn.execute( "UPDATE deals SET stage = ?, updated_at = ? WHERE id = ?", (new_stage, ts, args.deal_id), ) conn.execute( "INSERT INTO stage_history (deal_id, from_stage, to_stage, changed_at, notes) VALUES (?,?,?,?,?)", (args.deal_id, deal['stage'], new_stage, ts, args.note), ) if new_stage >= 6 and deal['stage'] < 6: n = conn.execute( "UPDATE commissions SET status = 'due', due_at = ? WHERE deal_id = ? AND status = 'agreed'", (ts, args.deal_id), ).rowcount if n: print(f" → {n} commission(s) auto-marked due (buyer paid).") elif new_stage < 6 and deal['stage'] >= 6: n = conn.execute( "UPDATE commissions SET status = 'agreed', due_at = NULL WHERE deal_id = ? AND status = 'due'", (args.deal_id,), ).rowcount if n: print(f" → {n} commission(s) rolled back to agreed (deal pre-payment again).") if new_stage == 8: conn.execute("UPDATE deals SET closed_at = ? WHERE id = ?", (ts, args.deal_id)) folder = Path(deal['folder_path']) if folder.exists() and folder.parent == ACTIVE_DIR: dst = CLOSED_DIR / folder.name if not dst.exists(): shutil.move(str(folder), str(dst)) conn.execute("UPDATE deals SET folder_path = ? WHERE id = ?", (str(dst), args.deal_id)) print(f"✓ {args.deal_id}: stage {deal['stage']} → {new_stage} ({STAGES[new_stage]})") regenerate_dashboard() def cmd_deal_leg_add(args): """Add a sell leg to an existing back-to-back deal.""" with db() as conn: deal = conn.execute("SELECT * FROM deals WHERE id = ?", (args.deal_id,)).fetchone() if not deal: print(f"Deal {args.deal_id} not found.") sys.exit(1) if deal['deal_type'] != 'back_to_back': print("Only back-to-back deals support multiple legs.") sys.exit(1) existing = get_legs(conn, args.deal_id) print(f"Current legs on {args.deal_id}:") for l in existing: print(f" [{l['id']}] {l['leg_type'].upper()} {l['leg_index']} — " f"{l['counterparty']} stage {l['stage']}. {STAGES.get(l['stage'], '?')} " f"vol={l['volume_mt'] or '?'} MT @ ${l['price_usd'] or '?'}/MT") sell_cid = prompt_int("Buyer company ID for new sell leg", required=True) sc = conn.execute("SELECT * FROM companies WHERE id = ?", (sell_cid,)).fetchone() if not sc: print(f"Company {sell_cid} not found.") sys.exit(1) sell_vol = prompt_float("Volume MT") sell_price = prompt_float("Sell price USD/MT") leg_notes = prompt("Notes") idx = next_sell_index(conn, args.deal_id) ts = now() cur = conn.execute( """INSERT INTO deal_legs (deal_id, leg_type, leg_index, company_id, counterparty, stage, volume_mt, price_usd, price_unit, notes, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)""", (args.deal_id, 'sell', idx, sc['id'], sc['name'], 1, sell_vol, sell_price, deal['price_unit'] or 'MT', leg_notes, ts), ) conn.execute( "INSERT INTO deal_parties (deal_id, company_id, role, created_at) VALUES (?,?,?,?)", (args.deal_id, sc['id'], "buyer", ts), ) update_summary_stage(conn, args.deal_id, ts) print(f"✓ Sell leg {idx} added (leg id {cur.lastrowid}): {sc['name']} @ ${sell_price}/MT") regenerate_dashboard() def cmd_deal_close(args): args.stage = 8 args.note = args.note or "Deal closed" cmd_deal_advance(args) def cmd_deal_next(args): """Set or clear the next_action / blocker note on a deal.""" text = args.text if args.text is not None else prompt("Next action / blocker (empty to clear)") with db() as conn: if not conn.execute("SELECT 1 FROM deals WHERE id = ?", (args.deal_id,)).fetchone(): print(f"Deal {args.deal_id} not found.") sys.exit(1) conn.execute( "UPDATE deals SET next_action = ?, updated_at = ? WHERE id = ?", (text or None, now(), args.deal_id), ) print(f"✓ Next action set on {args.deal_id}: {text or '(cleared)'}") regenerate_dashboard() def cmd_deal_party_add(args): """Link an existing company as a party to a deal.""" with db() as conn: if not conn.execute("SELECT 1 FROM deals WHERE id = ?", (args.deal_id,)).fetchone(): print(f"Deal {args.deal_id} not found.") sys.exit(1) company_id = prompt_int("Company ID (from 'company list')", required=True) role = prompt("Role", required=True, options=PARTY_ROLES) contact_id = prompt_int("Contact ID (optional)") bank_name = prompt("Bank name") bank_iban = prompt("IBAN / account no") bank_swift = prompt("SWIFT/BIC") bank_account_name = prompt("Bank account name") notes = prompt("Notes") with db() as conn: cur = conn.execute( """INSERT INTO deal_parties (deal_id, company_id, contact_id, role, bank_name, bank_iban, bank_swift, bank_account_name, notes, created_at) VALUES (?,?,?,?,?,?,?,?,?,?)""", (args.deal_id, company_id, contact_id, role, bank_name, bank_iban, bank_swift, bank_account_name, notes, now()), ) pid = cur.lastrowid print(f"✓ Party #{pid} added to deal.") regenerate_dashboard() # ── Commission, document, comm log ─────────────────────────────────────────── def cmd_commission_add(args): with db() as conn: deal = conn.execute("SELECT * FROM deals WHERE id = ?", (args.deal_id,)).fetchone() party = conn.execute( """SELECT dp.*, c.name AS company_name FROM deal_parties dp JOIN companies c ON c.id = dp.company_id WHERE dp.id = ? AND dp.deal_id = ?""", (args.party_id, args.deal_id) ).fetchone() if not deal or not party: print("Deal or party not found.") sys.exit(1) amount = prompt_float("Amount per unit (USD)", required=True) unit = prompt("Unit", default=deal['price_unit'] or "MT") total_default = (amount * (deal['volume_mt'] or 0)) if deal['volume_mt'] else None total = prompt_float("Total amount (USD)", default=f"{total_default:.2f}" if total_default else None) notes = prompt("Notes") with db() as conn: conn.execute( """INSERT INTO commissions (deal_id, deal_party_id, amount_per_unit, unit, total_amount, notes, created_at) VALUES (?,?,?,?,?,?,?)""", (args.deal_id, args.party_id, amount, unit, total, notes, now()), ) print(f"✓ Commission added for {party['company_name']}: ${amount:.2f}/{unit}") regenerate_dashboard() def cmd_commission_list(args): with db() as conn: rows = conn.execute( """SELECT cm.*, c.name AS party_name, c.id AS company_id, dp.role, d.commodity, d.id AS deal_id_chk FROM commissions cm JOIN deal_parties dp ON dp.id = cm.deal_party_id JOIN companies c ON c.id = dp.company_id JOIN deals d ON d.id = cm.deal_id ORDER BY CASE cm.status WHEN 'due' THEN 0 WHEN 'agreed' THEN 1 ELSE 2 END, cm.total_amount DESC""" ).fetchall() if not rows: print("No commissions logged.") return if args.by_commissioner: agg = {} for r in rows: cid = r['company_id'] if cid not in agg: agg[cid] = {'name': r['party_name'], 'deals': set(), 'total': 0.0, 'agreed': 0.0, 'due': 0.0, 'paid': 0.0} agg[cid]['deals'].add(r['deal_id']) amt = r['total_amount'] or 0 agg[cid]['total'] += amt agg[cid][r['status']] += amt print(f"\n{'Commissioner':35s} {'Deals':>6s} {'Total':>14s} {'Agreed':>14s} {'Due':>14s} {'Paid':>14s}") print("-" * 110) for cid, v in sorted(agg.items(), key=lambda x: -x[1]['total']): print(f"{v['name'][:35]:35s} {len(v['deals']):>6d} " f"${v['total']:>12,.2f} ${v['agreed']:>12,.2f} " f"${v['due']:>12,.2f} ${v['paid']:>12,.2f}") else: print(f"\n{'Deal':28s} {'Commissioner':25s} {'Role':14s} " f"{'Per unit':>10s} {'Total':>12s} {'Status':>8s}") print("-" * 105) for r in rows: print(f"{r['deal_id'][:28]:28s} {r['party_name'][:25]:25s} {r['role']:14s} " f"${r['amount_per_unit']:>8,.2f}/{r['unit']:<2s} " f"${r['total_amount'] or 0:>10,.2f} {r['status'].upper():>8s}") def cmd_commission_pay(args): ts = now() with db() as conn: row = conn.execute( """SELECT cm.*, d.stage AS deal_stage, d.id AS deal_id_check FROM commissions cm JOIN deals d ON d.id = cm.deal_id WHERE cm.id = ?""", (args.commission_id,) ).fetchone() if not row: print(f"Commission {args.commission_id} not found.") sys.exit(1) if row['deal_stage'] < 7 and not args.force: print(f"⚠ Deal {row['deal_id_check']} is at stage {row['deal_stage']}. " f"Commissions are only paid at stage 7 (Commission settlement).") print(f" Advance the deal first, or use --force to override.") sys.exit(1) conn.execute( "UPDATE commissions SET status = 'paid', paid_at = ? WHERE id = ?", (ts, args.commission_id), ) print(f"✓ Commission {args.commission_id} marked paid.") regenerate_dashboard() def cmd_commission_due(args): """Manually mark commission(s) due — usually auto-set when deal hits stage 6.""" ts = now() with db() as conn: conn.execute( "UPDATE commissions SET status = 'due', due_at = ? WHERE id = ?", (ts, args.commission_id), ) print(f"✓ Commission {args.commission_id} marked due.") regenerate_dashboard() def cmd_doc_add(args): target_inquiry = getattr(args, 'inquiry', False) with db() as conn: if target_inquiry: target = conn.execute("SELECT * FROM inquiries WHERE id = ?", (args.target_id,)).fetchone() stage = None else: target = conn.execute("SELECT * FROM deals WHERE id = ?", (args.target_id,)).fetchone() stage = target['stage'] if target else None if not target: print(f"Target not found.") sys.exit(1) doc_type = prompt("Doc type", required=True, options=["ICPO", "FCO", "SCO", "LOI", "NCNDA", "IMFPA", "SPA", "CI", "POP", "MT103", "Commission", "Other"]) filename = prompt("Filename") signed = prompt("Signed?", default="n", options=["y", "n"]) == "y" notes = prompt("Notes") with db() as conn: if target_inquiry: conn.execute( """INSERT INTO documents (inquiry_id, doc_type, filename, signed, notes, created_at) VALUES (?,?,?,?,?,?)""", (args.target_id, doc_type, filename, 1 if signed else 0, notes, now()), ) else: conn.execute( """INSERT INTO documents (deal_id, doc_type, stage, filename, signed, notes, created_at) VALUES (?,?,?,?,?,?,?)""", (args.target_id, doc_type, stage, filename, 1 if signed else 0, notes, now()), ) print(f"✓ Document logged: {doc_type}") regenerate_dashboard() def cmd_comm_log(args): target_inquiry = getattr(args, 'inquiry', False) with db() as conn: if target_inquiry: if not conn.execute("SELECT 1 FROM inquiries WHERE id = ?", (args.target_id,)).fetchone(): print("Inquiry not found.") sys.exit(1) else: if not conn.execute("SELECT 1 FROM deals WHERE id = ?", (args.target_id,)).fetchone(): print("Deal not found.") sys.exit(1) direction = prompt("Direction", required=True, options=["in", "out"]) channel = prompt("Channel", required=True, options=["email", "teams", "whatsapp", "phone", "other"]) subject = prompt("Subject") summary = prompt("Summary") ts = now() with db() as conn: if target_inquiry: conn.execute( """INSERT INTO comms_log (inquiry_id, direction, channel, subject, summary, timestamp, created_at) VALUES (?,?,?,?,?,?,?)""", (args.target_id, direction, channel, subject, summary, ts, ts), ) else: conn.execute( """INSERT INTO comms_log (deal_id, direction, channel, subject, summary, timestamp, created_at) VALUES (?,?,?,?,?,?,?)""", (args.target_id, direction, channel, subject, summary, ts, ts), ) print("✓ Comm logged.") regenerate_dashboard() def cmd_dashboard(args): regenerate_dashboard() print(f"✓ Dashboard regenerated: {DASHBOARD_PATH}") # ── Dashboard ─────────────────────────────────────────────────────────────── DASHBOARD_TEMPLATE = """ i4Fuel Pipeline

i4Fuel Pipeline

Generated {ts} · {db_path}
{alerts}
Open inquiries{n_inbox}
Active deals{n_active}
Companies{n_companies}
DD needed{n_dd_needed}
Flagged companies{n_flagged}
Active contract value${total_value:,.0f}
Commissions due now${comm_due:,.0f}
Commissions paid${comm_paid:,.0f}

Deal kanban

{kanban_columns}
{dd_queue_section}

Open inquiries

{inbox_table}

Recently rejected / converted

{inbox_closed_table}

Active deals ({n_active})

{deals_active_table}

Closed deals ({n_closed})

{deals_closed_table}
Active commissioners{n_commissioners}
Total commissions${comm_total:,.0f}
Agreed (future)${comm_agreed:,.0f}
Due now${comm_due:,.0f}
Paid${comm_paid:,.0f}

Commissioner leaderboard

{commissioners_table}

All commissions

{commissions_table}

All companies

{companies_table}

Flagged companies (suspect / scammer / blacklisted)

{flagged_table}

Recent incidents

{incidents_table}
{detail_modals}
""" REPORTS_DIR = SCRIPT_DIR / "reports" def _dd_report_link(path): """Render the DD report path as a relative link (Safari-sandbox safe). Reports live in SCRIPT_DIR/reports/ which is alongside dashboard.html.""" if not path: return "—" p = Path(path) bare = p.name if not p.is_absolute() else p.name candidates = [] stem = Path(bare).stem for name in (bare, f"{stem}.pdf", f"{stem}.html"): full = REPORTS_DIR / name if full.exists() and full not in candidates: candidates.append(full) if not candidates: return f'{he(path)} (file not found)' parts = [] for f in candidates: rel = f"reports/{f.name}" abs_path = str(f) parts.append( f'{he(f.name)} ' f'' ) return " · ".join(parts) def he(s): if s is None: return "" return (str(s).replace("&", "&").replace("<", "<") .replace(">", ">").replace('"', """)) def badge(cls, text): return f'{he(text)}' def regenerate_dashboard(): with db() as conn: deals = conn.execute("SELECT * FROM deals ORDER BY updated_at DESC").fetchall() all_legs = conn.execute( "SELECT * FROM deal_legs ORDER BY leg_type DESC, leg_index ASC" ).fetchall() companies = conn.execute("SELECT * FROM companies ORDER BY name").fetchall() inquiries = conn.execute("SELECT * FROM inquiries ORDER BY created_at DESC").fetchall() incidents = conn.execute( """SELECT i.*, c.name AS company_name FROM incidents i JOIN companies c ON c.id = i.company_id ORDER BY date DESC LIMIT 30""" ).fetchall() all_parties = conn.execute( """SELECT dp.*, c.name AS company_name, c.trust_level, c.dd_status FROM deal_parties dp JOIN companies c ON c.id = dp.company_id""" ).fetchall() all_comms = conn.execute( """SELECT cm.*, c.name AS party_name FROM commissions cm JOIN deal_parties dp ON dp.id = cm.deal_party_id JOIN companies c ON c.id = dp.company_id""" ).fetchall() all_docs = conn.execute("SELECT * FROM documents ORDER BY created_at DESC").fetchall() all_logs = conn.execute("SELECT * FROM comms_log ORDER BY timestamp DESC").fetchall() legs_by_deal = {} for leg in all_legs: legs_by_deal.setdefault(leg['deal_id'], []).append(leg) parties_by_deal = {} for p in all_parties: parties_by_deal.setdefault(p['deal_id'], []).append(p) comms_by_deal = {} for c in all_comms: comms_by_deal.setdefault(c['deal_id'], []).append(c) docs_by_deal = {} for d in all_docs: if d['deal_id']: docs_by_deal.setdefault(d['deal_id'], []).append(d) logs_by_deal = {} for l in all_logs: if l['deal_id']: logs_by_deal.setdefault(l['deal_id'], []).append(l) incidents_by_company = {} with db() as conn: for i in conn.execute("SELECT * FROM incidents ORDER BY date DESC").fetchall(): incidents_by_company.setdefault(i['company_id'], []).append(i) active_deals = [d for d in deals if d['stage'] < 8] closed_deals = [d for d in deals if d['stage'] == 8] open_inquiries = [i for i in inquiries if i['status'] == 'open'] closed_inquiries = [i for i in inquiries if i['status'] != 'open'] flagged_companies = [c for c in companies if c['trust_level'] in ('suspect', 'scammer', 'blacklisted')] dd_needed = [c for c in companies if c['dd_status'] in ('not_checked', 'in_progress', 'flagged')] total_value = sum((d['price_usd'] or 0) * (d['volume_mt'] or 0) for d in active_deals) comm_agreed = sum(c['total_amount'] or 0 for c in all_comms if c['status'] == 'agreed') comm_due = sum(c['total_amount'] or 0 for c in all_comms if c['status'] == 'due') comm_paid = sum(c['total_amount'] or 0 for c in all_comms if c['status'] == 'paid') comm_total = comm_agreed + comm_due + comm_paid comm_pending = comm_agreed + comm_due # legacy for overview stat # Commissioner aggregation — group commissions by company across all deals commissioners = {} # company_id -> aggregated stats deal_by_id = {d['id']: d for d in deals} for c in all_comms: party = next((p for p in all_parties if p['id'] == c['deal_party_id']), None) if not party: continue cid = party['company_id'] if cid not in commissioners: commissioners[cid] = { 'company_id': cid, 'company_name': party['company_name'], 'trust_level': party['trust_level'], 'deal_count': set(), 'total': 0.0, 'agreed': 0.0, 'due': 0.0, 'paid': 0.0, } commissioners[cid]['deal_count'].add(c['deal_id']) amt = c['total_amount'] or 0 commissioners[cid]['total'] += amt commissioners[cid][c['status']] += amt commissioner_list = sorted(commissioners.values(), key=lambda x: x['total'], reverse=True) # Alerts alerts_html = [] bl_companies = sorted( [c for c in companies if c['trust_level'] == 'blacklisted'], key=lambda c: c['created_at'], reverse=True, ) if bl_companies: most_recent = bl_companies[0]['name'] count = len(bl_companies) label = "company" if count == 1 else "companies" alerts_html.append( f'
' f'BLACKLISTED: {count} {label}. ' f'Most recent: {he(most_recent)}. ' f'Click to view all →
' ) if dd_needed: alerts_html.append( f'
' f'{len(dd_needed)} companies need DD. ' f'Click for action queue →
' ) # Kanban # Assign a stable accent color per B2B deal so paired cards are visually linked. B2B_ACCENTS = ["#f59e0b", "#818cf8", "#34d399", "#f472b6", "#38bdf8", "#fb923c", "#a3e635"] b2b_deals = [d for d in deals if d['deal_type'] == 'back_to_back'] b2b_color = {d['id']: B2B_ACCENTS[i % len(B2B_ACCENTS)] for i, d in enumerate(b2b_deals)} # Build per-column card lists. # B2B deals contribute one card to the buy-leg column and one to the sell-leg column. # Single deals contribute one card to their stage column. col_cards = {s: [] for s in range(1, 9)} for d in [x for x in deals if x['stage'] < 8]: parties = parties_by_deal.get(d['id'], []) flagged = any(p['trust_level'] in ('suspect', 'scammer', 'blacklisted') or p['dd_status'] != 'passed' for p in parties) na_html = (f'
⏳ {he(d["next_action"])}
' if d['next_action'] else '') if d['deal_type'] == 'back_to_back': accent = b2b_color[d['id']] legs = legs_by_deal.get(d['id'], []) klass = "card b2b-card" + (" flagged" if flagged else "") for leg in legs: leg_stage = min(leg['stage'], 7) vol_str = f"{leg['volume_mt']:,.0f} MT" if leg['volume_mt'] else "—" price_str = f"${leg['price_usd']:,.2f}/{leg['price_unit'] or 'MT'}" if leg['price_usd'] else "—" leg_label = f"▼ BUY LEG" if leg['leg_type'] == 'buy' else f"▲ SELL LEG {leg['leg_index']}" col_cards[leg_stage].append(f"""
{leg_label}
{he(d['id'])}
{he(d['commodity'])}
{he(leg['counterparty'])}
{vol_str}{price_str}
{na_html}
""") else: role_meta = { "buyer": ("▼ BUYER", "#38bdf8"), "seller": ("▲ SELLER", "#34d399"), "intermediary": ("⇄ INTERMEDIARY", "#f59e0b"), } role_label, role_color = role_meta.get(d['our_role'], (d['our_role'].upper(), "#94a3b8")) volume_str = f"{d['volume_mt']:,.0f} MT" if d['volume_mt'] else "—" price_str = f"${d['price_usd']:,.2f}/{d['price_unit'] or 'MT'}" if d['price_usd'] else "—" klass = "card" + (" flagged" if flagged else "") col_cards[d['stage']].append(f"""
{role_label}
{he(d['id'])}
{he(d['commodity'])}
{he(d['counterparty_name'])}
{volume_str}{price_str}
{na_html}
""") columns_html = [] for stage_num in range(1, 9): cards = col_cards[stage_num] or ['
—
'] columns_html.append(f"""

{stage_num}. {STAGES[stage_num]}{len(col_cards[stage_num])}

{''.join(cards)}
""") # Tables def deal_row(d): parties = parties_by_deal.get(d['id'], []) flagged = any(p['trust_level'] in ('suspect', 'scammer', 'blacklisted') or p['dd_status'] != 'passed' for p in parties) flag = '⚠ ' if flagged else '' na = (f'⏳ {he(d["next_action"])}' if d['next_action'] else '—') if d['deal_type'] == 'back_to_back': stage_cell = (f'B2B · buy:{d["buy_leg_stage"]} sell:{d["sell_leg_stage"]}') cp_cell = f'{he(d["counterparty_name"])} → {he(d["sell_leg_counterparty"] or "?")}' role_cell = 'back_to_back' else: stage_cell = f'{d["stage"]}. {he(STAGES[d["stage"]])}' cp_cell = he(d['counterparty_name']) role_cell = he(d['our_role']) return f""" {flag}{he(d['id'])} {stage_cell} {he(d['commodity'])} {cp_cell} {role_cell} {(d['volume_mt'] or 0):,.0f} ${(d['price_usd'] or 0):,.2f}/{he(d['price_unit'] or '')} {na} {he(d['updated_at'][:10])} """ deal_header = ("""StageCommodityCounterparty RoleVol/MTPrice Next actionUpdated""") deals_active_html = ( f"{deal_header}{''.join(deal_row(d) for d in active_deals)}
" if active_deals else '

No active deals.

' ) deals_closed_html = ( f"{deal_header}{''.join(deal_row(d) for d in closed_deals)}
" if closed_deals else '

No closed deals.

' ) # Inbox table def inquiry_row(i): return f""" #{i['id']} {he(i['direction'])} {he(i['type'])} {he(i['channel'])} {he(i['raw_company_name'] or '—')} {he(i['commodity_code'] or '—')} {(i['volume_mt'] or 0):,.0f} ${(i['price_usd'] or 0):,.2f}/{he(i['price_unit'] or '')} {badge(f"status-{i['status']}", i['status'])} {he((i['next_action'] or '')[:40])} {he((i['received_at'] or '')[:10])} """ inquiry_header = ("""IDDirTypeChannelCompany CommodityVol/MTPriceStatus Next actionReceived""") inbox_table = ( f"{inquiry_header}{''.join(inquiry_row(i) for i in open_inquiries)}
" if open_inquiries else '

No open inquiries.

' ) inbox_closed_table = ( f"{inquiry_header}{''.join(inquiry_row(i) for i in closed_inquiries[:30])}
" if closed_inquiries else '

None.

' ) # Commissioner leaderboard def commissioner_row(c): return f""" {he(c['company_name'])} {badge(f"trust-{c['trust_level']}", c['trust_level'])} {len(c['deal_count'])} ${c['total']:,.2f} ${c['agreed']:,.2f} ${c['due']:,.2f} ${c['paid']:,.2f} """ commissioners_table = ( f"" f"" f"{''.join(commissioner_row(c) for c in commissioner_list)}
CommissionerTrustDealsTotalAgreedDuePaid
" if commissioner_list else '

No commissions logged yet.

' ) # All commissions table def commission_row(cm): party = next((p for p in all_parties if p['id'] == cm['deal_party_id']), None) d = deal_by_id.get(cm['deal_id']) status_class = f"comm-{cm['status']}" status_text = cm['status'].upper() timestamp = cm['paid_at'] or cm['due_at'] or cm['created_at'] return f""" {he(cm['deal_id'])} {he(d['commodity']) if d else '—'} {he(party['company_name']) if party else '—'} {he(party['role']) if party else '—'} ${cm['amount_per_unit']:,.2f}/{he(cm['unit'])} ${cm['total_amount'] or 0:,.2f} {badge(status_class, status_text)} {he((timestamp or '')[:10])} """ # Sort: due first (urgent), then agreed (future), then paid (history); within each by amount desc status_order = {'due': 0, 'agreed': 1, 'paid': 2} sorted_commissions = sorted( all_comms, key=lambda c: (status_order.get(c['status'], 9), -(c['total_amount'] or 0)) ) commissions_table = ( f"" f"" f"{''.join(commission_row(c) for c in sorted_commissions)}
DealCommodityCommissionerRolePer unitTotalStatusPaid
" if sorted_commissions else '

No commissions logged yet.

' ) # DD queue section (companies needing DD with action buttons) cli_path = f'python3 "{SCRIPT_DIR / "pipeline.py"}"' # Index contacts and inquiry contact strings by company_id contacts_by_company = {} with db() as conn: for ct in conn.execute("SELECT * FROM contacts").fetchall(): contacts_by_company.setdefault(ct['company_id'], []).append(ct) inquiry_contacts_by_company = {} for inq in inquiries: if inq['company_id'] and inq['raw_contact']: inquiry_contacts_by_company.setdefault(inq['company_id'], []).append(inq['raw_contact']) def dd_queue_row(c): # Build a /supplier-check prompt with everything we know prompt_lines = [f"/supplier-check {c['name']}"] prompt_lines.append("") prompt_lines.append("Context:") if c['country']: prompt_lines.append(f"- Country: {c['country']}") if c['registration_no']: prompt_lines.append(f"- Registration: {c['registration_no']}") if c['website']: prompt_lines.append(f"- Website: {c['website']}") if c['email']: prompt_lines.append(f"- Email: {c['email']}") if c['phone']: prompt_lines.append(f"- Phone: {c['phone']}") if c['address']: prompt_lines.append(f"- Address: {c['address']}") # Add full contact records + raw contacts from inquiries contact_lines = [] for ct in contacts_by_company.get(c['id'], []): bits = [ct['name']] if ct['title']: bits.append(ct['title']) if ct['email']: bits.append(ct['email']) if ct['phone']: bits.append(ct['phone']) if ct['nationality']: bits.append(ct['nationality']) contact_lines.append(" / ".join(bits)) for raw in inquiry_contacts_by_company.get(c['id'], []): if raw and raw not in contact_lines: contact_lines.append(raw) if contact_lines: prompt_lines.append("- Contact person(s):") for cl in contact_lines: prompt_lines.append(f" · {cl}") supplier_prompt = "\n".join(prompt_lines) cli_cmd = f"{cli_path} company check {c['id']}" # JS-safe encoding for data attribute sp_b64 = supplier_prompt.replace('\\', '\\\\').replace("'", "\\'").replace("\n", "\\n") cli_b64 = cli_cmd.replace("'", "\\'") return f""" {he(c['name'])} {he(c['country'] or '—')} {badge(f"trust-{c['trust_level']}", c['trust_level'])} {badge(f"dd-{c['dd_status']}", c['dd_status'])} """ if dd_needed: dd_queue_section = f"""

DD action queue ({len(dd_needed)})

Click a button to copy the command. Step 1: paste /supplier-check into Claude chat to run the check. Step 2: paste the CLI command into terminal to record the result.

{''.join(dd_queue_row(c) for c in dd_needed)}
CompanyCountryTrustDDActions
""" else: dd_queue_section = "" # Companies table def company_row(c): bl = '⚠' if c['blacklist_folder_match'] else '' return f""" {he(c['name'])} {he(c['country'] or '—')} {he(c['registration_no'] or '—')} {badge(f"trust-{c['trust_level']}", c['trust_level'])} {badge(f"dd-{c['dd_status']}", c['dd_status'])} {he(c['dd_score'] or '')} {badge(f"ofac-{c['ofac_status']}", c['ofac_status'])} {bl} """ company_header = ("""NameCountryReg no TrustDDOFACBL""") companies_table = ( f"{company_header}{''.join(company_row(c) for c in companies)}
" if companies else '

No companies yet.

' ) flagged_table = ( f"{company_header}{''.join(company_row(c) for c in flagged_companies)}
" if flagged_companies else '

No flagged companies.

' ) # Incidents table def incident_row(i): return f""" {he(i['date'][:10])} {he(i['company_name'])} {he(i['type'])} {he((i['description'] or '')[:80])} """ incidents_table = ( f"" f"{''.join(incident_row(i) for i in incidents)}
DateCompanyTypeDescription
" if incidents else '

No incidents logged.

' ) # Modals modals_html = [] # Deal modals for d in deals: parties = parties_by_deal.get(d['id'], []) comms = comms_by_deal.get(d['id'], []) docs = docs_by_deal.get(d['id'], []) logs = logs_by_deal.get(d['id'], []) parties_rows = "".join( f"{badge('role', p['role'])}" f"{he(p['company_name'])}" f"{badge(f'trust-{p['trust_level']}', p['trust_level'])}" f"{badge(f'dd-{p['dd_status']}', p['dd_status'])}" f"{he(p['bank_name'] or '')}" for p in parties ) or "No parties yet." comms_rows = "".join( f"{he(c['party_name'])}" f"${c['amount_per_unit']:,.2f}/{he(c['unit'])}" f"${c['total_amount'] or 0:,.2f}" f"{badge(f'comm-{c['status']}', c['status'].upper())}" f"{he((c['paid_at'] or c['due_at'] or '')[:10])}" for c in comms ) or "No commissions logged." docs_rows = "".join( f"{he(doc['doc_type'])}{doc['stage'] or ''}" f"{he(doc['filename'] or '')}" f"{'✓' if doc['signed'] else ''}" f"{he(doc['created_at'][:10])}" for doc in docs ) or "No documents." logs_rows = "".join( f"{he(l['timestamp'][:16])}" f"{'→' if l['direction']=='out' else '←'}" f"{he(l['channel'])}" f"{he(l['subject'] or '')}" f"{he(l['summary'] or '')}" for l in logs[:20] ) or "No comms." # Use a path relative to dashboard.html so file:// works on any user's # machine (no hardcoded user dirs). dashboard.html lives in SCRIPT_DIR. folder_link = '—' if d['folder_path']: try: rel = str(Path(d['folder_path']).relative_to(SCRIPT_DIR)) folder_link = f'Open folder' except ValueError: folder_link = f'{he(d["folder_path"])} (not under Pipeline/)' d_legs = legs_by_deal.get(d['id'], []) if d['deal_type'] == 'back_to_back': legs_summary = " · ".join( f"{'Buy' if l['leg_type']=='buy' else 'Sell '+str(l['leg_index'])}: " f"{he(l['counterparty'])} s{l['stage']}" for l in d_legs ) deal_subtitle = f"{he(d['commodity'])} · Back-to-back · {legs_summary}" legs_rows = "".join( f"{'BUY' if l['leg_type']=='buy' else 'SELL '+str(l['leg_index'])}" f"{he(l['counterparty'])}" f"{l['stage']}. {he(STAGES.get(l['stage'],'?'))}" f"{(l['volume_mt'] or 0):,.0f} MT" f"${(l['price_usd'] or 0):,.2f}/{he(l['price_unit'] or 'MT')}" f"{he(l['notes'] or '')}" for l in d_legs ) role_row = ( f'
Entity
{he(ENTITIES[d["our_entity"]]["name"])}
' f'
Legs
' f'
' f'' f'{legs_rows}
LegCounterpartyStageVolumePriceNotes
' ) else: deal_subtitle = f"{he(d['commodity'])} · {he(d['counterparty_name'])} · Stage {d['stage']}. {he(STAGES[d['stage']])}" role_row = f'
Our role
{he(d["our_role"])} ({he(ENTITIES[d["our_entity"]]["name"])})
' modals_html.append(f"""

{he(d['id'])}

{deal_subtitle}
{role_row}
Trial / Monthly
{(d['volume_mt'] or 0):,.0f} MT / {(d['monthly_mt'] or 0):,.0f} MT
Price
${(d['price_usd'] or 0):,.2f} per {he(d['price_unit'] or 'MT')}
Incoterm
{he(d['incoterm'] or '—')}
Port / Origin
{he(d['port'] or '—')} / {he(d['origin'] or '—')}
Payment terms
{he(d['payment_terms'] or '—')}
Folder
{folder_link}
From inquiry
{f'#{d["inquiry_id"]}' if d['inquiry_id'] else '—'}
Next action / blocker
{he(d['next_action'] or '—')}
Notes
{he(d['notes'] or '—')}

Parties

{parties_rows}
RoleCompanyTrustDDBank

Commissions

{comms_rows}
PartyPer unitTotalStatusPaid

Documents

{docs_rows}
TypeStageFilenameSignedLogged

Recent comms

{logs_rows}
TimeDirChannelSubjectSummary
""") # Inquiry modals for i in inquiries: modals_html.append(f"""

Inquiry #{i['id']}

{he(i['direction'])} · {he(i['type'])} · {he(i['channel'])} · {badge(f"status-{i['status']}", i['status'])}
Company
{he(i['raw_company_name'] or '—')} {f'(#{i["company_id"]})' if i['company_id'] else ''}
Contact
{he(i['raw_contact'] or '—')}
Commodity
{he(i['commodity_code'] or '—')}
Volume
{(i['volume_mt'] or 0):,.0f} MT (monthly {(i['monthly_mt'] or 0):,.0f} MT)
Price
${(i['price_usd'] or 0):,.2f}/{he(i['price_unit'] or 'MT')}
Incoterm
{he(i['incoterm'] or '—')}
Origin / Port
{he(i['origin'] or '—')} → {he(i['port'] or '—')}
Payment
{he(i['payment_terms'] or '—')}
Received
{he((i['received_at'] or '')[:16])}
Next action
{he(i['next_action'] or '—')}
Deal ID
{he(i['deal_id'] or '—')}
Rejection reason
{he(i['rejection_reason'] or '—')}
Notes
{he(i['notes'] or '—')}
""") # Company modals for c in companies: c_incidents = incidents_by_company.get(c['id'], []) c_contacts = contacts_by_company.get(c['id'], []) contact_rows = "".join( f"{he(ct['name'])}{he(ct['title'] or '—')}" f"{he(ct['email'] or '—')}{he(ct['phone'] or '—')}" f"{he(ct['nationality'] or '—')}" for ct in c_contacts ) or "No contacts." inc_rows = "".join( f"{he(inc['date'][:10])}{he(inc['type'])}{he(inc['description'] or '')}" for inc in c_incidents ) or "No incidents." c_deals = [d for d in deals if d['counterparty_company_id'] == c['id'] or any(p['company_id'] == c['id'] for p in parties_by_deal.get(d['id'], []))] deal_rows = "".join( f"{he(d['id'])}" f"{d['stage']}. {he(STAGES[d['stage']])}{he(d['commodity'])}" for d in c_deals ) or "No deals." modals_html.append(f"""

{he(c['name'])}

{badge(f"trust-{c['trust_level']}", c['trust_level'])} {badge(f"dd-{c['dd_status']}", c['dd_status'])} {badge(f"ofac-{c['ofac_status']}", c['ofac_status'])}
Country
{he(c['country'] or '—')}
Registration
{he(c['registration_no'] or '—')}
Address
{he(c['address'] or '—')}
Website
{he(c['website'] or '—')}
Email / Phone
{he(c['email'] or '—')} / {he(c['phone'] or '—')}
DD score
{he(c['dd_score'] or '—')}
DD report
{_dd_report_link(c['dd_report_path'])}
DD checked at
{he((c['dd_checked_at'] or '')[:10] or '—')}
OFAC details
{he(c['ofac_hit_details'] or '—')}
Blacklist match
{he(c['blacklist_folder_match'] or 'no match')}
Notes
{he(c['notes'] or '—')}

Contacts

{contact_rows}
NameTitleEmailPhoneNationality

Incidents

{inc_rows}
DateTypeDescription

Deals

{deal_rows}
IDStageCommodity
""") replacements = { "{ts}": now()[:16].replace("T", " "), "{db_path}": str(DB_PATH), "{n_inbox}": str(len(open_inquiries)), "{n_active}": str(len(active_deals)), "{n_closed}": str(len(closed_deals)), "{n_companies}": str(len(companies)), "{n_flagged}": str(len(flagged_companies)), "{n_dd_needed}": str(len(dd_needed)), "{n_commissions}": str(len(all_comms)), "{n_commissioners}": str(len(commissioner_list)), "{total_value:,.0f}": f"{total_value:,.0f}", "{comm_agreed:,.0f}": f"{comm_agreed:,.0f}", "{comm_due:,.0f}": f"{comm_due:,.0f}", "{comm_paid:,.0f}": f"{comm_paid:,.0f}", "{comm_total:,.0f}": f"{comm_total:,.0f}", "{commissioners_table}": commissioners_table, "{commissions_table}": commissions_table, "{alerts}": "".join(alerts_html), "{kanban_columns}": "".join(columns_html), "{dd_queue_section}": dd_queue_section, "{inbox_table}": inbox_table, "{inbox_closed_table}": inbox_closed_table, "{deals_active_table}": deals_active_html, "{deals_closed_table}": deals_closed_html, "{companies_table}": companies_table, "{flagged_table}": flagged_table, "{incidents_table}": incidents_table, "{detail_modals}": "".join(modals_html), } html = DASHBOARD_TEMPLATE for k, v in replacements.items(): html = html.replace(k, v) DASHBOARD_PATH.write_text(html) # ── CLI dispatch ───────────────────────────────────────────────────────────── def main(): parser = argparse.ArgumentParser( prog="pipeline", description="i4Fuel deal pipeline (v2 — companies, inquiries, incidents, DD)", formatter_class=argparse.RawDescriptionHelpFormatter, epilog=textwrap.dedent(""" common flow: pipeline.py inquiry new new inbound FCO/RFQ pipeline.py company check record DD result pipeline.py inquiry convert promote to deal pipeline.py deal advance next stage pipeline.py deal close other: pipeline.py inquiry list [--all] pipeline.py inquiry reject --reason "..." pipeline.py inquiry link --company pipeline.py company new pipeline.py company list [--needs-dd|--flagged] pipeline.py company show pipeline.py company trust --level scammer pipeline.py company ofac pipeline.py company incident pipeline.py contact new pipeline.py deal new outbound origination pipeline.py deal list [--closed] pipeline.py deal show pipeline.py deal party add pipeline.py comm add --party pipeline.py comm pay pipeline.py doc add [--inquiry] pipeline.py log [--inquiry] pipeline.py dashboard """), ) sub = parser.add_subparsers(dest="cmd", required=True) # inquiry inq = sub.add_parser("inquiry") inq_sub = inq.add_subparsers(dest="action", required=True) inq_sub.add_parser("new") inq_list = inq_sub.add_parser("list") inq_list.add_argument("--all", action="store_true") inq_show = inq_sub.add_parser("show") inq_show.add_argument("id", type=int) inq_conv = inq_sub.add_parser("convert") inq_conv.add_argument("id", type=int) inq_conv.add_argument("--force", action="store_true") inq_rej = inq_sub.add_parser("reject") inq_rej.add_argument("id", type=int) inq_rej.add_argument("--reason", default=None) inq_link = inq_sub.add_parser("link") inq_link.add_argument("id", type=int) inq_link.add_argument("--company", type=int, required=True, dest="company_id") # company co = sub.add_parser("company") co_sub = co.add_subparsers(dest="action", required=True) co_sub.add_parser("new") co_list = co_sub.add_parser("list") co_list.add_argument("--needs-dd", action="store_true") co_list.add_argument("--flagged", action="store_true") co_show = co_sub.add_parser("show") co_show.add_argument("id", type=int) co_check = co_sub.add_parser("check") co_check.add_argument("id", type=int) co_trust = co_sub.add_parser("trust") co_trust.add_argument("id", type=int) co_trust.add_argument("--level", choices=TRUST_LEVELS) co_ofac = co_sub.add_parser("ofac") co_ofac.add_argument("id", type=int) co_inc = co_sub.add_parser("incident") co_inc.add_argument("id", type=int) # contact ct = sub.add_parser("contact") ct_sub = ct.add_subparsers(dest="action", required=True) ct_new = ct_sub.add_parser("new") ct_new.add_argument("company_id", type=int) # deal deal = sub.add_parser("deal") deal_sub = deal.add_subparsers(dest="action", required=True) deal_sub.add_parser("new") d_list = deal_sub.add_parser("list") d_list.add_argument("--closed", action="store_true") d_show = deal_sub.add_parser("show") d_show.add_argument("deal_id") d_adv = deal_sub.add_parser("advance") d_adv.add_argument("deal_id") d_adv.add_argument("--stage", type=int) d_adv.add_argument("--leg", type=int, default=None, help="For back-to-back deals: deal_legs.id to advance") d_adv.add_argument("--note", default=None) d_close = deal_sub.add_parser("close") d_close.add_argument("deal_id") d_close.add_argument("--note", default=None) d_next = deal_sub.add_parser("next") d_next.add_argument("deal_id") d_next.add_argument("--text", default=None) d_leg = deal_sub.add_parser("leg") d_leg_sub = d_leg.add_subparsers(dest="action2", required=True) d_leg_add = d_leg_sub.add_parser("add") d_leg_add.add_argument("deal_id") d_party = deal_sub.add_parser("party") d_party_sub = d_party.add_subparsers(dest="action2", required=True) d_party_add = d_party_sub.add_parser("add") d_party_add.add_argument("deal_id") # commission comm = sub.add_parser("comm") comm_sub = comm.add_subparsers(dest="action", required=True) c_add = comm_sub.add_parser("add") c_add.add_argument("deal_id") c_add.add_argument("--party", type=int, required=True, dest="party_id") c_pay = comm_sub.add_parser("pay") c_pay.add_argument("commission_id", type=int) c_pay.add_argument("--force", action="store_true") c_due = comm_sub.add_parser("due") c_due.add_argument("commission_id", type=int) c_list = comm_sub.add_parser("list") c_list.add_argument("--by-commissioner", action="store_true") # doc doc = sub.add_parser("doc") doc_sub = doc.add_subparsers(dest="action", required=True) d_add = doc_sub.add_parser("add") d_add.add_argument("target_id") d_add.add_argument("--inquiry", action="store_true") # log log = sub.add_parser("log") log.add_argument("target_id") log.add_argument("--inquiry", action="store_true") sub.add_parser("dashboard") args = parser.parse_args() init_db() handlers = { ("inquiry", "new"): cmd_inquiry_new, ("inquiry", "list"): cmd_inquiry_list, ("inquiry", "show"): cmd_inquiry_show, ("inquiry", "convert"): cmd_inquiry_convert, ("inquiry", "reject"): cmd_inquiry_reject, ("inquiry", "link"): cmd_inquiry_link, ("company", "new"): cmd_company_new, ("company", "list"): cmd_company_list, ("company", "show"): cmd_company_show, ("company", "check"): cmd_company_check, ("company", "trust"): cmd_company_trust, ("company", "ofac"): cmd_company_ofac, ("company", "incident"): cmd_company_incident, ("contact", "new"): cmd_contact_new, ("deal", "new"): cmd_deal_new, ("deal", "list"): cmd_deal_list, ("deal", "show"): cmd_deal_show, ("deal", "advance"): cmd_deal_advance, ("deal", "close"): cmd_deal_close, ("deal", "next"): cmd_deal_next, ("comm", "add"): cmd_commission_add, ("comm", "pay"): cmd_commission_pay, ("comm", "due"): cmd_commission_due, ("comm", "list"): cmd_commission_list, ("doc", "add"): cmd_doc_add, } key = (args.cmd, getattr(args, "action", None)) if args.cmd == "dashboard": cmd_dashboard(args) elif args.cmd == "log": cmd_comm_log(args) elif args.cmd == "deal" and args.action == "leg": cmd_deal_leg_add(args) elif args.cmd == "deal" and args.action == "party": cmd_deal_party_add(args) elif key in handlers: handlers[key](args) else: parser.print_help() sys.exit(1) if __name__ == "__main__": main()