#!/usr/bin/env python3
"""Build the optional, fully fictional offline budget what-if worksheet."""
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.workbook.properties import CalcProperties

out = Path(__file__).resolve().parent / 'budget-what-if.xlsx'
w = Workbook()
s = w.active
s.title = 'Invented weeks'
s['A1'] = 'Fictional weekly budget what-if · edit only yellow inputs; no personal data'
s.merge_cells('A1:G1')
s['A1'].font = Font(size=14, bold=True, color='FFFFFF')
s['A1'].fill = PatternFill('solid', fgColor='234D70')
s['A2'] = 'Week'; s['B2'] = 'Income'; s['C2'] = 'Fixed'; s['D2'] = 'Variable essential'; s['E2'] = 'Discretionary'; s['F2'] = 'Unassigned'; s['G2'] = 'Model $200 target'
for c in s[2]:
    c.font = Font(bold=True, color='FFFFFF')
    c.fill = PatternFill('solid', fgColor='126358')
    c.alignment = Alignment(wrap_text=True, vertical='center')
for i, row in enumerate([('A',1260,680,220,110),('B',1260,680,250,125),('C',1260,700,230,85)], 3):
    for col, value in enumerate(row, 1):
        cell = s.cell(i, col, value)
        if col in (2,3,4,5):
            cell.fill = PatternFill('solid', fgColor='FFF0B8')
            cell.number_format = '$#,##0.00'
    s.cell(i, 6, f'=B{i}-C{i}-D{i}-E{i}').number_format = '$#,##0.00'
    s.cell(i, 7, f'=IF(F{i}>=200,"MEETS","BELOW")')
    s.cell(i, 7).alignment = Alignment(horizontal='center')
    for col in range(1,8):
        s.cell(i, col).border = Border(bottom=Side(style='hair', color='B7CBC7'))
s['A7'] = 'Rule'; s['B7'] = 'unassigned = income − fixed − variable essential − discretionary'
s.merge_cells('B7:G7')
s['A8'] = 'Try'; s['B8'] = 'Change D4 from 250 to 260, read F4 and G4, then restore 250.'
s.merge_cells('B8:G8')
s['A9'] = 'Boundary'; s['B9'] = 'Gross is stipulated as available in this exercise only; real deductions and omitted costs are unknown.'
s.merge_cells('B9:G9')
s['A10'] = 'Access'; s['B10'] = 'Card G and budget-map.pdf are paper/text equivalents. Formula cells are visible in the formula bar.'
s.merge_cells('B10:G10')
s['A11'] = 'Data'; s['B11'] = 'All values are invented. Do not enter personal or household figures.'
s.merge_cells('B11:G11')
for row in range(7,12):
    s.cell(row,1).font = Font(bold=True)
    s.cell(row,2).alignment = Alignment(wrap_text=True, vertical='center')
    s.row_dimensions[row].height = 31
for col,width in {'A':15,'B':18,'C':16,'D':23,'E':19,'F':19,'G':23}.items():
    s.column_dimensions[col].width=width
s.row_dimensions[2].height=40
s.freeze_panes='B3'
s.print_options.horizontalCentered=True
s.sheet_properties.pageSetUpPr.fitToPage=True
s.page_setup.paperSize=s.PAPERSIZE_A4
s.page_setup.orientation=s.ORIENTATION_LANDSCAPE
s.page_margins.left=0.3
s.page_margins.right=0.3
s.page_setup.fitToWidth=1
s.page_setup.fitToHeight=1
s.print_area='A1:G11'
w.calculation = CalcProperties(calcMode='auto', fullCalcOnLoad=True)
w.save(out)
print(out)