import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = openpyxl.Workbook()

# ── Sheet: Employees ──────────────────────────────────────────
ws1 = wb.active
ws1.title = "Employees"
headers1 = ["ID", "Name", "Department", "Salary", "HiredDate"]
ws1.append(headers1)
employees = [
    [1, "Alice Chen",    "Engineering",   95000, "2021-03-15"],
    [2, "Bob Martinez",  "Marketing",     78000, "2022-07-01"],
    [3, "Carol Singh",   "Engineering",  105000, "2020-11-20"],
    [4, "Dave Okafor",   "Sales",         72000, "2023-01-10"],
    [5, "Eve Thompson",  "HR",            65000, "2022-09-05"],
    [6, "Frank Wu",      "Engineering",  115000, "2019-06-17"],
    [7, "Grace Kim",     "Marketing",     82000, "2021-12-01"],
    [8, "Henry Patel",   "Sales",         88000, "2020-04-22"],
]
for row in employees:
    ws1.append(row)
tab1 = Table(displayName="Employees", ref=f"A1:E{len(employees)+1}")
tab1.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
ws1.add_table(tab1)

# ── Sheet: Products ───────────────────────────────────────────
ws2 = wb.create_sheet("Products")
headers2 = ["ID", "Name", "Category", "Price", "InStock"]
ws2.append(headers2)
products = [
    [101, "Widget Alpha",   "Widgets",   9.99,  250],
    [102, "Widget Beta",    "Widgets",  14.99,  180],
    [103, "Gadget Gamma",   "Gadgets",  29.99,   75],
    [104, "Gadget Delta",   "Gadgets",  49.99,   42],
    [105, "Doohickey Eps",  "Doohickeys", 5.99,  500],
    [106, "Doohickey Zeta", "Doohickeys", 7.99,  320],
    [107, "Widget Eta",     "Widgets",   19.99,    0],
    [108, "Gadget Theta",   "Gadgets",  39.99,   15],
]
for row in products:
    ws2.append(row)
tab2 = Table(displayName="Products", ref=f"A1:E{len(products)+1}")
tab2.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
ws2.add_table(tab2)

# ── Sheet: Orders ─────────────────────────────────────────────
ws3 = wb.create_sheet("Orders")
headers3 = ["OrderID", "Customer", "ProductID", "Quantity", "OrderDate", "Status"]
ws3.append(headers3)
orders = [
    [1001, "Acme Corp",      101, 10, "2025-01-15", "Shipped"],
    [1002, "Globex Inc",     103,  5, "2025-01-17", "Delivered"],
    [1003, "Initech",        106, 20, "2025-02-01", "Pending"],
    [1004, "Acme Corp",      102,  8, "2025-02-10", "Shipped"],
    [1005, "Umbrella Co",    107, 15, "2025-03-05", "Cancelled"],
    [1006, "Globex Inc",     108,  3, "2025-03-12", "Processing"],
    [1007, "Initech",        104,  2, "2025-04-01", "Delivered"],
    [1008, "Acme Corp",      105, 50, "2025-04-15", "Pending"],
    [1009, "Umbrella Co",    101, 12, "2025-05-01", "Shipped"],
    [1010, "Globex Inc",     104,  7, "2025-05-20", "Processing"],
]
for row in orders:
    ws3.append(row)
tab3 = Table(displayName="Orders", ref=f"A1:F{len(orders)+1}")
tab3.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
ws3.add_table(tab3)

wb.save("data/sample.xlsx")
print("Generated data/sample.xlsx with 3 tables (Employees, Products, Orders)")