Manager — Weekly KPI Dashboard From Sheets

from pathlib import Path

def _find_notebooks_dir() -> Path:
    cwd = Path.cwd().resolve()
    if cwd.name == 'notebooks':
        return cwd
    candidate = cwd / 'notebooks'
    return candidate if candidate.is_dir() else cwd
WORKSPACE = _find_notebooks_dir() / '_manager_workspace'
WORKSPACE.mkdir(parents=True, exist_ok=True)
print('workspace:', WORKSPACE)

# from shipit_agent.llms import build_llm_from_settings
# llm = build_llm_from_settings({'provider': 'bedrock',
#     'model': 'bedrock/openai.gpt-oss-120b-1:0'}, provider='bedrock')
# llm = build_llm_from_settings({'provider': 'litellm',
#     'model': 'anthropic/claude-sonnet-4-5'}, provider='litellm')
# from shipit_agent.llms import LiteLLMProxyChatLLM
# llm = LiteLLMProxyChatLLM(model='gpt-4o-mini',
#     api_base='https://litellm.internal', api_key='sk-proxy')

from shipit_agent.llms import SimpleEchoLLM
llm = SimpleEchoLLM()
print('llm:', type(llm).__name__)

from shipit_agent.integrations import CredentialRecord, InMemoryCredentialStore
from shipit_agent.tools.google_sheets import GoogleSheetsTool
from shipit_agent.tools.slack import SlackTool

store = InMemoryCredentialStore()
store.set(CredentialRecord(
    key='google_sheets', provider='google_sheets',
    secrets={'access_token': 'sheets-demo'},
    metadata={'base_url': 'https://sheets.googleapis.com'},
))
store.set(CredentialRecord(
    key='slack', provider='slack', secrets={'token': 'xoxb-demo'},
))

sheets = GoogleSheetsTool(credential_store=store)
slack = SlackTool(credential_store=store)

SHEET_ID = '1ABC_demo_spreadsheet_id'

# Canned values for three ranges.
_SHEET_VALUES = {
    'Weekly!A2:B4': [
        ['Velocity (points)', '42'],
        ['Deploys', '17'],
        ['Error rate (%)', '0.32'],
    ],
    'Weekly!A8:C13': [
        ['Squad',    'Deploys', 'Velocity'],
        ['Platform', '6',       '14'],
        ['Billing',  '4',       '11'],
        ['Growth',   '3',       '9'],
        ['Mobile',   '2',       '5'],
        ['DX',       '2',       '3'],
    ],
}

def _fake_sheets(*, record, method, path, query=None, body=None):
    # Decode the range name from /values/<encoded-range> path.
    # Canned payloads key off the exact range-string shape Sheets uses.
    for range_str, values in _SHEET_VALUES.items():
        from urllib.parse import quote
        if quote(range_str, safe='') in path:
            return {'range': range_str, 'majorDimension': 'ROWS', 'values': values}
    return {'values': []}

sheets._request_json = _fake_sheets

def _fake_slack(*, record, method, path, query=None, body=None):
    if path == '/chat.postMessage':
        return {'ok': True, 'channel': body.get('channel'),
                'ts': '1714000000.000200'}
    return {'ok': True}
slack._request_json = _fake_slack
print('stubs installed')

from shipit_agent.tools.base import ToolContext

ctx = ToolContext(prompt='manager kpi', state={'credential_store': store})

headline = sheets.run(ctx, action='get_values',
                       spreadsheet_id=SHEET_ID, range='Weekly!A2:B4')
print(headline.text)

per_squad = sheets.run(ctx, action='get_values',
                        spreadsheet_id=SHEET_ID, range='Weekly!A8:C13')
print()
print(per_squad.text)

from shipit_agent.tools.dashboard_render import DashboardRenderTool

dash = DashboardRenderTool(workspace_root=WORKSPACE)

kpi_rows = headline.metadata.get('values') or []
headline_items = [
    {'label': r[0], 'value': r[1]} for r in kpi_rows if len(r) >= 2
]

sq_rows = per_squad.metadata.get('values') or []
squad_bars = []
if len(sq_rows) > 1:
    rows = sq_rows[1:]
    # Normalize velocity to percent for bars.
    max_v = max((int(r[2]) for r in rows if len(r) >= 3 and r[2].isdigit()), default=1)
    palette = ['#185fa5', '#1d9e75', '#534ab7', '#ba7517', '#888888']
    for i, r in enumerate(rows):
        if len(r) < 3 or not r[2].isdigit():
            continue
        pct = int(round(int(r[2]) / max_v * 100))
        squad_bars.append({'label': r[0], 'pct': pct, 'color': palette[i % len(palette)]})

result = dash.run(
    ToolContext(prompt='manager kpi',
                 state={'artifact_workspace_root': str(WORKSPACE)}),
    title='Engineering KPIs — week of 2026-04-20',
    subtitle='Generated from the team Google Sheet',
    lang='en',
    sections=[
        {'type': 'metrics', 'title': 'Headline', 'columns': len(headline_items),
         'items': headline_items},
        {'type': 'bars', 'title': 'Velocity by squad', 'items': squad_bars},
        {'type': 'verdict', 'title': 'Manager takeaway',
         'text': ('Platform leads velocity for the third week running. '
                  'Mobile + DX are holding — flag for investment review.')},
    ],
    export=True,
)
print(result.text)
print('artifact path:', result.metadata.get('path'))

text = (
    '*Engineering KPIs — week of 2026-04-20*\n'
    + '\n'.join(f'{i["label"]}: *{i["value"]}*' for i in headline_items)
    + '\n\nFull dashboard attached.'
)
slack_out = slack.run(ctx, action='post_message',
                       channel='C_ENG_UPDATES', text=text)
print(slack_out.text)