#!/usr/bin/env python3
"""Generated OfficeCLI script for Q2_Budget.xlsx"""

import json, subprocess, tempfile, os

FILE = "Q2_Budget.xlsx"


def batch(commands):
    with tempfile.NamedTemporaryFile(mode="w", suffix=".json", delete=False) as f:
        json.dump(commands, f)
        f.flush()
        result = subprocess.run(
            ["officecli", "batch", FILE, "--input", f.name],
            capture_output=True, text=True
        )
        os.unlink(f.name)
        if result.returncode != 0:
            print(result.stderr or result.stdout)
            raise RuntimeError(f"Batch failed: {result.returncode}")
        print(result.stdout, end="")


subprocess.run(["officecli", "create", FILE], check=True)

# Turn 1: budget-turn1
batch(json.loads('[\n  {\n    "command": "set",\n    "path": "/Sheet1",\n    "props": {\n      "name": "Departments"\n    }\n  },\n  {\n    "command": "add",\n    "parent": "/",\n    "type": "sheet",\n    "props": {\n      "name": "Summary"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A1",\n    "props": {\n      "value": "Department",\n      "bold": "true"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B1",\n    "props": {\n      "value": "Q1 Actual",\n      "bold": "true"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C1",\n    "props": {\n      "value": "Q2 Budget",\n      "bold": "true"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D1",\n    "props": {\n      "value": "Q2 Actual",\n      "bold": "true"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E1",\n    "props": {\n      "value": "Variance",\n      "bold": "true"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A2",\n    "props": {\n      "value": "Sales"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B2",\n    "props": {\n      "value": "120000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C2",\n    "props": {\n      "value": "135000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D2",\n    "props": {\n      "value": "128000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E2",\n    "props": {\n      "formula": "=D2-C2"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A3",\n    "props": {\n      "value": "Marketing"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B3",\n    "props": {\n      "value": "45000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C3",\n    "props": {\n      "value": "50000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D3",\n    "props": {\n      "value": "52000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E3",\n    "props": {\n      "formula": "=D3-C3"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A4",\n    "props": {\n      "value": "Operations"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B4",\n    "props": {\n      "value": "80000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C4",\n    "props": {\n      "value": "85000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D4",\n    "props": {\n      "value": "83000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E4",\n    "props": {\n      "formula": "=D4-C4"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A5",\n    "props": {\n      "value": "HR"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B5",\n    "props": {\n      "value": "30000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C5",\n    "props": {\n      "value": "32000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D5",\n    "props": {\n      "value": "31000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E5",\n    "props": {\n      "formula": "=D5-C5"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A6",\n    "props": {\n      "value": "IT"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B6",\n    "props": {\n      "value": "55000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C6",\n    "props": {\n      "value": "60000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D6",\n    "props": {\n      "value": "58000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E6",\n    "props": {\n      "formula": "=D6-C6"\n    }\n  }\n]'))

# Turn 2: budget-turn2
batch(json.loads('[\n  {\n    "command": "add",\n    "parent": "/Departments",\n    "type": "row",\n    "index": 4\n  },\n  {\n    "command": "set",\n    "path": "/Departments/A4",\n    "props": {\n      "value": "Product"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/B4",\n    "props": {\n      "value": "60000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/C4",\n    "props": {\n      "value": "65000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D4",\n    "props": {\n      "value": "63000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E4",\n    "props": {\n      "formula": "=D4-C4"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/D2",\n    "props": {\n      "value": "138000"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments/E2",\n    "props": {\n      "formula": "=D2-C2"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/A1",\n    "props": {\n      "value": "Q2 Budget Summary"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/A3",\n    "props": {\n      "value": "Total Q1 Actual"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/B3",\n    "props": {\n      "formula": "=SUM(Departments!B2:B7)"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/A4",\n    "props": {\n      "value": "Total Q2 Budget"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/B4",\n    "props": {\n      "formula": "=SUM(Departments!C2:C7)"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/A5",\n    "props": {\n      "value": "Total Q2 Actual"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/B5",\n    "props": {\n      "formula": "=SUM(Departments!D2:D7)"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/A6",\n    "props": {\n      "value": "Total Variance"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary/B6",\n    "props": {\n      "formula": "=SUM(Departments!E2:E7)"\n    }\n  }\n]'))

# Turn 3: budget-turn3
batch(json.loads('[\n  {\n    "command": "add",\n    "parent": "/Departments",\n    "type": "chart",\n    "props": {\n      "chartType": "bar",\n      "title": "Q2 Budget vs Actual by Department",\n      "series1.name": "Q2 Budget",\n      "series1.values": "Departments!C2:C7",\n      "series1.categories": "Departments!A2:A7",\n      "series2.name": "Q2 Actual",\n      "series2.values": "Departments!D2:D7",\n      "series2.categories": "Departments!A2:A7",\n      "catTitle": "Department",\n      "axisTitle": "Amount ($)",\n      "x": 6,\n      "y": 1,\n      "width": 10,\n      "height": 14,\n      "legend": true\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Departments",\n    "props": {\n      "freeze": "A2"\n    }\n  },\n  {\n    "command": "set",\n    "path": "/Summary",\n    "props": {\n      "freeze": "A2"\n    }\n  }\n]'))

subprocess.run(["officecli", "validate", FILE], check=True)
print(f"\nDone: {FILE}")