"""Lesson 7: Excel automation with openpyxl - several sheets, formulas, formats, freeze panes, a chart."""
import csv, json
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference
HERE = Path(__file__).resolve().parent
prod = json.load(open(HERE / "products.json"))
rows = [r for r in csv.DictReader(open(HERE / "sales.csv", encoding="utf-8")) if r["qty"] and r["date"][4] == "-"]
wb = Workbook(); ws = wb.active; ws.title = "Sales"
ws.append(["Invoice", "Date", "Code", "Qty", "Rate", "Amount", "GST"])
for i, r in enumerate(rows, start=2):
    p = prod[r["code"]]
    ws.append([r["invoice"], r["date"], r["code"], int(r["qty"]), p["price"], f"=D{i}*E{i}", f"=ROUND(F{i}*{p['gst']},2)"])
for c in ws[1]:
    c.font = Font(bold=True, color="FFFFFF"); c.fill = PatternFill("solid", fgColor="217346"); c.alignment = Alignment(horizontal="center")
for col in "EFG":
    for c in ws[col][1:]: c.number_format = "#,##0.00"
ws.freeze_panes = "A2"; ws.column_dimensions["A"].width = 11
last = ws.max_row
s = wb.create_sheet("Summary")
s.append(["Code", "Product", "Amount"])
for j, (code, p) in enumerate(prod.items(), start=2):
    s.append([code, p["name"], f"=SUMIFS(Sales!F:F,Sales!C:C,A{j})"])
s.append(["", "Total", f"=SUM(C2:C{j})"])
ch = BarChart(); ch.title = "Sales by product"; ch.add_data(Reference(s, min_col=3, min_row=1, max_row=j), titles_from_data=True)
ch.set_categories(Reference(s, min_col=2, min_row=2, max_row=j)); s.add_chart(ch, "E2")
out = HERE / "output"; out.mkdir(exist_ok=True); wb.save(out / "sales-report.xlsx")
print("Rows written:", last - 1, "| sheets:", wb.sheetnames, "| saved output/sales-report.xlsx")
print("Formula in F2:", ws["F2"].value, "| summary total:", s[f"C{j + 1}"].value)